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
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
相关阅读
问下微软社招面试,靠谱的回答一个包子答谢。GE 招人 @ Miwlaukee,Wisconsin大家一般几次on-site拿到一个offer啊?dataminr电面面经(已跪)起薪的重要性貌似L也不行了今年2个同学H1B adv pp,全部没中non-pp adv CSC reciept recievednew grad找不到工作。。。要去icc吗?google hr说要pre interview call新鲜的 G 的 offernon-pp ADV今天知道抽中消息ADV PP CSC H1B approved国内985计算机硕士,有绿卡。直接刷题,进BB和FLAG有希望吗?哥替老婆的offer求个bless名校的水硕还是有用的.从几个法案看 共和党和民主党对华人职业移民的态度 (转载)有人知道cal index 这个startup 么?H1B pending, OPT还没过期,应该怎么换工作关于自己人帮自己人