Question

我在将两个表连接在一起时遇到了一点困难，涉及到第二个表中没有包含的SUM。

也就是说，我有两张桌子：

tblClient

 ID|FirstName|LastName
 1 |Billy    |Blogs
 2 |Bobby    |Biggs
 3 |Hill     |Bill
 4 |Super    |Man

tblComplete

 PurchaseID|ID|Amount
 123123    |1 |26.00
 43254     |2 |22.00
 546275    |3 |15.00
 463565    |3 |15.00
 343252    |1 |56.00

我想做的是将这两个表连接在一起，这样我就得到了的输出：

 ID|FirstName|LastName| SUM(Amount)
 1 |Billy    |Blogs   | 82.00
 2 |Bobby    |Biggs   | 22.00
 3 |Hill     |Bill    | 30.00
 4 |Super    |Man     | 0

我希望Super Man被包括在输出中，总和为零，因为他没有购买任何东西。我该怎么做？

EDIT：tblComplete表中的主键。

Answer 1

select
  CL.ID,
  CL.FirstName,
  CL.LastName,
  coalesce(CO.SumAmount, 0) as [SUM(Amount)]
from tblClient as CL
  left outer join
    (
      select
        sum(Amount) as SumAmount,
        ID
      from tblComplete
      group by ID
    ) as CO
    on CL.ID = CO.ID

Answer 2

SQL Server:

您用一个来自tblClient的select开始您的语句，它为您的输出中的每个记录包含一条记录。然后您LEFT JOIN，您可以对ClientID执行分组，并使用聚合函数SUM（）来汇总每个客户端的数量（如果存在的话）。

SELECT
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName, 
  Sum(ISNULL(COMP.Amount, 0)) as Sum 
FROM 
  tblClient as CLIENT 
  LEFT JOIN tblComplete as COMP on COMP.ID = CLIENT.ID 
GROUP BY 
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName

Answer 3

简单的答案是，使用OUTER JOIN或LEFT JOIN（因为LEFT JOINS以..开头是外部的）

查看更多：http://en.wikipedia.org/wiki/Join_（SQL）#Outer_joins

关键字outer makes是include行，即使它们在两个表中都没有记录。LEFT或RIGHT实际上只是＜code＞＝＜/code＞的哪一边有空表。

Answer 4

试试whit LEFT JOIN？

SELECT
    tblClient.*,
    SUM(tblComplete.amount) as Amount
FROM
    tblClient
LEFT JOIN
    tblComplete ON tblClient.ID = tblComplete.ID
GROUP BY
    tblClient.id

如果这不起作用（我不执行它），请尝试：

SELECT
    tblClient.*,
    IF(COUNT(tblComplete.*), SUM(tblComplete.amount), 0) as Amount
FROM
    tblClient
LEFT JOIN
    tblComplete ON tblClient.ID = tblComplete.ID
GROUP BY
    tblClient.id

Answer 5

select a.ID,a.FirstName,a.LastName, sum(b.Amount)  from tblClient a , tblComplete b where a.ID = b.ID group by b.ID

Answer 6

在选择列表中使用聚合函数

SELECT
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName, 
  Sum(ISNULL(COMP.Amount, 0)) as Sum
FROM 
  tblClient as CLIENT 
  LEFT JOIN tblComplete as COMP on COMP.ID = CLIENT.ID 
GROUP BY 
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName

或

SELECT
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName, 
  Sum(ISNULL(COMP.Amount, 0)) as Sum 
FROM 
  tblClient as CLIENT 
  Inner JOIN tblComplete as COMP on COMP.ID = CLIENT.ID 
GROUP BY 
  CLIENT.ID, 
  CLIENT.FirstName, 
  CLIENT.LastName

tblClient

tblComplete

友情链接