l******9
发帖数: 579 | 1 【 以下文字转载自 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. | m*********u
发帖数: 1491 | 2 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 | l*********8
发帖数: 4642 | 3 在你的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
| l******9
发帖数: 579 | 4 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
| l*********8
发帖数: 4642 | 5 请给几个例子吧
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
发帖数: 579 | 6 I have updated my post ! thanks !
【在 l*********8 的大作中提到】
: 请给几个例子吧
:
: int
: from
| l*********8
发帖数: 4642 | 7 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 | l******9
发帖数: 579 | 8 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
| l*********8
发帖数: 4642 | 9 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*********8
发帖数: 4642 | 10 Please let me know if if works.
【在 l*********8 的大作中提到】
: No. top_id are different in two SQLs.
| l******9
发帖数: 579 | 11 It works. Thansk ! But, i have a new question.
【在 l*********8 的大作中提到】
: Please let me know if if works.
|
|
