Sql Comparsion两个表 [英] Sql Comparsion two tables

问题描述

嗨Frnds，

我有2张桌子如下。

Hi Frnds,
I have 2 tables as follows.

  Table1
EmpId    EmpName

 1         A
 2         B
 3         C
   Table2
PayId   PayAmount

 1     1000
 2     2000

 Table3
PayId     EmpId     PaidAmount  PaidDate

  1         1          300       21/5/2010
  1         1          300       11/6/2012
  2         1         1000       12/10/2012
  1         2         1000       28/4/2012
  2         2         2000       01/06/2012

当我通过payId时（例如：PayId = 1）到表3 ...

我需要输出如下...

when i pass payId(for eg:PayId=1) to Table3...
I need output as follows...

EmpId    PayId PayAmount PaidAmount 
  1         1      1000     600
  2         1      2000    1000
  3         1      1000       0

谢谢ASAP

Thanks ASAP

推荐答案

试试这个

Try this

SELECT T3.EmpId,T3.PayId,Table2.PayAmount,T3.PaidAmount FROM
(SELECT EmpId,PayId,SUM(PaidAmount) AS PaidAmount
FROM Table3
GROUP BY EmpId, PayID) AS T3
INNER JOIN Table2
ON Table2.PayId=T3.PayId 

输出

Output

EmpId       PayId       PayAmount   PaidAmount
----------- ----------- ----------- -----------
1           1           1000        600
2           1           1000        1000
1           2           2000        1000
2           2           2000        2000

检查你的样品输出，似乎不正确。

------ -------------------------------------------------- ----------------------------



嗯，关于你的意见，我假设每个员工加入每个PayID。因此我们必须使用 CROSS JOIN

Check your sample output, it seems not correct.
------------------------------------------------------------------------------------

Well, With regarding your comments, I assumed each employee join with each PayID. Therefore we have to use CROSS JOIN for that

SELECT Table1.EmpId,Table1.EmpName,Table2.PayId,Table2.PayAmount,ISNULL(PaidAmount,0)AS PaidAmount
FROM Table1
CROSS JOIN Table2
LEFT OUTER JOIN 
(SELECT EmpId,PayId,SUM(PaidAmount) AS PaidAmount
FROM Table3
GROUP BY EmpId, PayID) AS T3
ON T3.PayId =Table2.PayId AND T3.EmpId = Table1.EmpId
WHERE Table2.PayId=1

你会得到的以下输出

you will get hte following output

EmpId       EmpName PayId       PayAmount   PaidAmount
----------- ------- ----------- ----------- -----------
1           A       1           1000        600
2           B       1           1000        1000
3           C       1           1000        0

请检查您的样品输出，支付EmpId 2的金额是不正确的。如果您通过PayID = 1

这篇关于Sql Comparsion两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！