代码之家 › 专栏 › 技术社区 › kodym

SQL连接两个查询和类和记录

db2 vb.net asp.net sql

-1

kodym · 技术社区 · 6 年前

我正在使用asp编写一个web表单页。网我有两张不同的桌子。这两个表都有相同的列,只是数据不同。例如:

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

我知道连接语法已经过时了,但这家公司就是这样使用的。如果您需要更多信息,请告诉我,并提前感谢您的回复!

2 回复 | 直到 6 年前

Zohar Peled 6 年前

好的,使用coalesce和完全外部连接基本上相当容易,尽管我不知道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 supports implicit left joins and implicit right joins 。我找不到有关完全联接的任何信息,但我想它应该是这样工作的:

FROM table1 as t1, table2 as t2
WHERE t1.col1(+) = t2.col1(+)

Satya 6 年前

在“分组依据”中的5列中,仅选择了2列。对于给定的号码和代表,您可能会获得多条记录。请检查“UNION ALL”是否满足您的要求。

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