l*9
2 楼
【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to combine two tables into one. Ans also, add a column (assign an int
value) to the new table on SQL. So that the rows from table1 and ones from
table2 are assigned with different values.
Example,
table1
ID1 ID2 ID3 VALUE
table2
ID1 ID2 ID3 VALUE
table3
ID1 ID2 ID3 VALUE
i need to combine table3 and table2 into a new table and add a new column
table_new
top_id ID2 ID3 new_value
It is Netezza SQL.
INSERT INTO new_table
SELECT *
FROM
(
SELECT '80' AS top_id, * , sum (table1.VALUE * table2.VALUE) AS new_
value
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table2.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, call it as top_id
and also assign an int value to it, such as 80
UNION ALL
SELECT *
FROM
(
SELECT '81' AS top_id, * , sum (table1.VALUE * table3.VALUE) AS new_
value
FROM table1
JOIN
table3
ON table1.id1 = table3.id1
GROUP BY table3.id2, table3.id3
) AS tt_b # here, I need to add a new column to tt, call it as top_id
and also assign an int value to it, such as 81
ORDER BY top_id
I use "order by top_id", I got error:
ERROR [HY000] ERROR: 0 : Functionality not implemented
I em new to SQL.
Any help would be appreciated.
发信人: light009 (light009), 信区: Database
标 题: SQL combine two tables into one table and add a new column
发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
I need to combine two tables into one. Ans also, add a column (assign an int
value) to the new table on SQL. So that the rows from table1 and ones from
table2 are assigned with different values.
Example,
table1
ID1 ID2 ID3 VALUE
table2
ID1 ID2 ID3 VALUE
table3
ID1 ID2 ID3 VALUE
i need to combine table3 and table2 into a new table and add a new column
table_new
top_id ID2 ID3 new_value
It is Netezza SQL.
INSERT INTO new_table
SELECT *
FROM
(
SELECT '80' AS top_id, * , sum (table1.VALUE * table2.VALUE) AS new_
value
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table2.id2, table2.id3
) AS tt_a # here, I need to add a new column to tt, call it as top_id
and also assign an int value to it, such as 80
UNION ALL
SELECT *
FROM
(
SELECT '81' AS top_id, * , sum (table1.VALUE * table3.VALUE) AS new_
value
FROM table1
JOIN
table3
ON table1.id1 = table3.id1
GROUP BY table3.id2, table3.id3
) AS tt_b # here, I need to add a new column to tt, call it as top_id
and also assign an int value to it, such as 81
ORDER BY top_id
I use "order by top_id", I got error:
ERROR [HY000] ERROR: 0 : Functionality not implemented
I em new to SQL.
Any help would be appreciated.
m*u
3 楼
SELECT *
FROM
(
SELECT *,'80' as top_id
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a
union all
(
SELECT *,'81' as top_id
FROM table1
JOIN
table3
ON table1.id1 = table3.id1
GROUP BY table3.id2, table3.id3
) AS tt_b
GROUP BY top_id, id2, id3
FROM
(
SELECT *,'80' as top_id
FROM table1
JOIN
table2
ON table1.id1 = table2.id1
GROUP BY table1.id2, table2.id3
) AS tt_a
union all
(
SELECT *,'81' as top_id
FROM table1
JOIN
table3
ON table1.id1 = table3.id1
GROUP BY table3.id2, table3.id3
) AS tt_b
GROUP BY top_id, id2, id3
l*8
4 楼
在你的sub query里面，已经用了group by， 就不能用select * 了吧，应该用
aggregate functions.
【在 l******9 的大作中提到】
: 【 以下文字转载自 Database 讨论区 】
: 发信人: light009 (light009), 信区: Database
: 标 题: SQL combine two tables into one table and add a new column
: 发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
: I need to combine two tables into one. Ans also, add a column (assign an int
: value) to the new table on SQL. So that the rows from table1 and ones from
: table2 are assigned with different values.
: Example,
: table1
: ID1 ID2 ID3 VALUE
aggregate functions.
【在 l******9 的大作中提到】
: 【 以下文字转载自 Database 讨论区 】
: 发信人: light009 (light009), 信区: Database
: 标 题: SQL combine two tables into one table and add a new column
: 发信站: BBS 未名空间站 (Thu May 8 14:54:50 2014, 美东)
: I need to combine two tables into one. Ans also, add a column (assign an int
: value) to the new table on SQL. So that the rows from table1 and ones from
: table2 are assigned with different values.
: Example,
: table1
: ID1 ID2 ID3 VALUE
l*9
5 楼
thanks for you reply, I have updated my post.
i need to do
sum (table1.VALUE * table3.VALUE) AS new_value
I got error:
ERROR [HY000] ERROR: Attribute tt_b.new_value must be GROUPed or used
in an aggregate function
【在 m*********u 的大作中提到】
: SELECT *
: FROM
: (
: SELECT *,'80' as top_id
: FROM table1
: JOIN
: table2
: ON table1.id1 = table2.id1
: GROUP BY table1.id2, table2.id3
: ) AS tt_a
i need to do
sum (table1.VALUE * table3.VALUE) AS new_value
I got error:
ERROR [HY000] ERROR: Attribute tt_b.new_value must be GROUPed or used
in an aggregate function
【在 m*********u 的大作中提到】
: SELECT *
: FROM
: (
: SELECT *,'80' as top_id
: FROM table1
: JOIN
: table2
: ON table1.id1 = table2.id1
: GROUP BY table1.id2, table2.id3
: ) AS tt_a
l*8
6 楼
请给几个例子吧
int
from
【在 l******9 的大作中提到】
: thanks for you reply, I have updated my post.
: i need to do
: sum (table1.VALUE * table3.VALUE) AS new_value
: I got error:
: ERROR [HY000] ERROR: Attribute tt_b.new_value must be GROUPed or used
: in an aggregate function
:
int
from
【在 l******9 的大作中提到】
: thanks for you reply, I have updated my post.
: i need to do
: sum (table1.VALUE * table3.VALUE) AS new_value
: I got error:
: ERROR [HY000] ERROR: Attribute tt_b.new_value must be GROUPed or used
: in an aggregate function
:
l*9
7 楼
I have updated my post ! thanks !
【在 l*********8 的大作中提到】
: 请给几个例子吧
:
: int
: from
【在 l*********8 的大作中提到】
: 请给几个例子吧
:
: int
: from
l*8
8 楼
Use two SQLs:
INSERT INTO new_table
SELECT '80', t2.id2, t3.id3, sum(t1.value * t2.value)
FROM table1 t1
JOIN table2 t2
ON t1.id1 = t2.id1
GROUP BY t2.id2, t2.id3
INSERT INTO new_table
SELECT '81', t3.id2, t3.id3, sum(t1.value * t3.value)
FROM table1 t1
JOIN table3 t3
ON t1.id1 = t3.id1
GROUP BY t3.id2, t3.id3
INSERT INTO new_table
SELECT '80', t2.id2, t3.id3, sum(t1.value * t2.value)
FROM table1 t1
JOIN table2 t2
ON t1.id1 = t2.id1
GROUP BY t2.id2, t2.id3
INSERT INTO new_table
SELECT '81', t3.id2, t3.id3, sum(t1.value * t3.value)
FROM table1 t1
JOIN table3 t3
ON t1.id1 = t3.id1
GROUP BY t3.id2, t3.id3
l*9
9 楼
in this way, the former rows will be replaced / removed by the later
inserted rows ?
Thanks !
【在 l*********8 的大作中提到】
: Use two SQLs:
: INSERT INTO new_table
: SELECT '80', t2.id2, t3.id3, sum(t1.value * t2.value)
: FROM table1 t1
: JOIN table2 t2
: ON t1.id1 = t2.id1
: GROUP BY t2.id2, t2.id3
: INSERT INTO new_table
: SELECT '81', t3.id2, t3.id3, sum(t1.value * t3.value)
: FROM table1 t1
inserted rows ?
Thanks !
【在 l*********8 的大作中提到】
: Use two SQLs:
: INSERT INTO new_table
: SELECT '80', t2.id2, t3.id3, sum(t1.value * t2.value)
: FROM table1 t1
: JOIN table2 t2
: ON t1.id1 = t2.id1
: GROUP BY t2.id2, t2.id3
: INSERT INTO new_table
: SELECT '81', t3.id2, t3.id3, sum(t1.value * t3.value)
: FROM table1 t1
l*8
10 楼
No. top_id are different in two SQLs.
【在 l******9 的大作中提到】
: in this way, the former rows will be replaced / removed by the later
: inserted rows ?
: Thanks !
【在 l******9 的大作中提到】
: in this way, the former rows will be replaced / removed by the later
: inserted rows ?
: Thanks !
l*8
11 楼
Please let me know if if works.
【在 l*********8 的大作中提到】
: No. top_id are different in two SQLs.
【在 l*********8 的大作中提到】
: No. top_id are different in two SQLs.
l*9
12 楼
It works. Thansk ! But, i have a new question.
【在 l*********8 的大作中提到】
: Please let me know if if works.
【在 l*********8 的大作中提到】
: Please let me know if if works.
相关阅读
内推Jr./Sr. Java && Senior Software Engineer (C++/Python)求Google Team Match【视频分享】Python成为Data Engineers/Scientists首选语言可以推迟新工作的开始时间吗？外F凶猛 (转载)果家的offer收到纸版后还能谈判吗没有CS学位也能找到工作么？湾区游戏公司招UI/UX码农申请 H-1B 可能会更困难了有人了解 SolarCity 公司吗？小印下手好狠有人做过hirebridge的assessment么？求助 面试工资问题diversify很重要 (转载)大新闻：明年所有的 IT 程序员可能不能申请H1B了 (转载)Dropbox要今年准备上市了140复印件需要现在公司允许？板上非cs专业做IT工作的州政府部门工作强度比小公司还大又被三婆恶心到了。谈谈经验