我正在使用 asp.net 编写一个网络表单页面。我有两个不同的表。这两个表具有相同的列,只是数据不同。例如:
Table 1: col1, col2, col3
123 $12.54 Tom
34 $7.00 Jan
Table 2: col1, col2, col3
123 $125.00 Tom
56 $12.00 Joe
我正在尝试编写一个将合并两个表的 sql 语句,如果表 1 中的 Col1 与表 2 中的 col1 相同,我想将第 2 列的值相加。例如:
Col1 col2 col3
123 $137.54 Tom
34 $7.00 Jan
56 $12.00 Joe
以下是 sql 查询:
表1:
SELECT ABAN8 as Number, SUM(SDAEXP * .01) as SaleAmount, A5POPN as Rep
FROM KAIPRDDTA.F0101, KAIPRDDTA.F4211, KAIPRDDTA.F0301
WHERE A5AN8 = ABAN8
AND ABAN8 = SDAN8
AND SDKCOO = '00001'
AND SDDCTO not like '%2'
AND A5DAOJ >= '118069'
AND A5DAOJ <= '118099'
GROUP BY ABALPH, A5POPN, ABAN8, A5UPMT, A5CMC1
表2:
SELECT ABAN8 as Number, SUM(SDAEXP * .01) as SaleAmount, A5POPN as Rep
FROM KAIPRDDTA.F0101, KAIPRDDTA.F42119, KAIPRDDTA.F0301
WHERE A5AN8 = ABAN8
AND ABAN8 = SDAN8
AND SDKCOO = '00001'
AND SDDCTO not like '%2'
AND A5DAOJ >= '118069'
AND A5DAOJ <= '118099'
GROUP BY ABALPH, A5POPN, ABAN8, A5UPMT, A5CMC1
我知道连接语法已经过时了,但这就是这家公司使用它的方式。如果您需要更多信息,请告诉我,并提前感谢您的回复!
嗯,使用合并和完全外部连接基本上相当容易,尽管我不知道 db2 是否支持隐式完全外部连接。
我将解决您发布的示例数据,而不是查询,因为我喜欢能够阅读我正在编写的内容,并且因为我觉得对于每个将要阅读本文的人来说,它会更加清晰 - 所以这里是:
SELECT COALESCE(t1.col1, t2.col1) As col1,
COALESCE(t1.col2, 0) + COALESCE(t2.col2, 0) As col2,
COALESCE(t1.col3, t2.col3) As col3
FROM table1 as t1
FULL JOIN table12 as t2 ON t1.col1 = t2.col1
据我了解,DB2 支持隐式左连接和隐式右连接。我找不到有关完全加入的任何信息,但我想它应该像这样工作:
FROM table1 as t1, table2 as t2
WHERE t1.col1(+) = t2.col1(+)
在"分组依据"的 5 列中,仅选择了 2 列。对于给定的数量和代表,您可能会获得多个记录。检查"联合全部"是否符合您的要求。
Select number, sum(SaleAmount) , Rep
from
( SELECT ABAN8 as Number, (SDAEXP * .01) as SaleAmount, A5POPN as Rep
FROM KAIPRDDTA.F0101, KAIPRDDTA.F4211, KAIPRDDTA.F0301
WHERE A5AN8 = ABAN8
AND ABAN8 = SDAN8
AND SDKCOO = '00001'
AND SDDCTO not like '%2'
AND A5DAOJ >= '118069'
AND A5DAOJ <= '118099'
union all
SELECT ABAN8 as Number, (SDAEXP * .01) as SaleAmount, A5POPN as Rep
FROM KAIPRDDTA.F0101, KAIPRDDTA.F42119, KAIPRDDTA.F0301
WHERE A5AN8 = ABAN8
AND ABAN8 = SDAN8
AND SDKCOO = '00001'
AND SDDCTO not like '%2'
AND A5DAOJ >= '118069'
AND A5DAOJ <= '118099'
) a
group by number , rep