MSSQL - 组合两个 SELECT，包括 JOIN 和 COUNT

Question

我是Stackoverflow的新手。希望我的问题不傻！

我有两个选择，我必须组合。我尝试过 UNION 和另一个 JOIN，但没有成功。

SELECT 
Person.Name, Saldo1.Duration AS Holiday, Saldo2.Duraten AS Hours
FROM Person_Saldo AS Saldo1 
JOIN Person_Saldo AS Saldo2 ON Saldo1.Person_ID = Saldo2.Person_ID
JOIN Person ON Saldo1.Person_ID = Person.ID
WHERE Person.Group_ID= '1' AND Person.Active = 'true' AND Saldo1.Year = '2017' AND Saldo1.Timekonto = '4' AND Saldo2.Timekonto='52'  

和第二个选择：

SELECT
Person_ID, Count([Date]) AS Amount 
FROM Booking Where Timekonto = 54 AND Booking_OK = 1 Group by Person_ID

第一个 SELECT 显示如下内容：

Name Holiday Hours A 20 50 B 12 -30

第二个是这样的：

ID Amount A 4 B 3

我尝试的：
Name Holiday Hours Amount A 20 50 4 B 12 -30 3

谢谢，希望你能帮助我。

Answer 1

一种方法是将聚合查询联接到主查询，如下所示：

SELECT 
Person.Name
, Saldo1.Duration AS Holiday
, Saldo2.Duraten AS Hours
, b.Amount
FROM Person_Saldo AS Saldo1 
JOIN Person_Saldo AS Saldo2 ON Saldo1.Person_ID = Saldo2.Person_ID
JOIN Person ON Saldo1.Person_ID = Person.ID
left join (
SELECT Person_ID, Count([Date]) AS Amount 
FROM Booking 
Where Timekonto = 54 
AND Booking_OK = 1 
Group by Person_ID
) b on b.Person_ID = Saldo1
WHERE Person.Group_ID= '1' 
AND Person.Active = 'true' 
AND Saldo1.Year = '2017' 
AND Saldo1.Timekonto = '4' 
AND Saldo2.Timekonto='52'

您可能希望使用left join而不是inner join，如果count ()可能null您仍希望在结果中返回的人员。

您也可以使用outer apply()或cross apply()来获得相同的结果。

SELECT 
Person.Name
, Saldo1.Duration AS Holiday
, Saldo2.Duraten AS Hours
, b.Amount
FROM Person_Saldo AS Saldo1 
JOIN Person_Saldo AS Saldo2 ON Saldo1.Person_ID = Saldo2.Person_ID
JOIN Person ON Saldo1.Person_ID = Person.ID
outer apply (
SELECT Count([Date]) AS Amount 
FROM Booking as i
Where i.Person_Id = Saldo1.PersonId
AND i.Timekonto = 54 
AND i.Booking_OK = 1 
) as b 
WHERE Person.Group_ID= '1' 
AND Person.Active = 'true' 
AND Saldo1.Year = '2017' 
AND Saldo1.Timekonto = '4' 
AND Saldo2.Timekonto='52'

Answer 2

您可以使用"With"以使其更清晰。可以在两个表之间使用左外部联接。

WITH Table2 as (
SELECT
Person_ID, Count([Date]) AS Amount 
FROM Booking Where Timekonto = 54 AND Booking_OK = 1 Group by Person_ID)

Select Name, Holiday, Hours, Amount
from (SELECT 
Person.Name, Saldo1.Duration AS Holiday, Saldo2.Duraten AS Hours
FROM Person_Saldo AS Saldo1 
JOIN Person_Saldo AS Saldo2 ON Saldo1.Person_ID = Saldo2.Person_ID
JOIN Person ON Saldo1.Person_ID = Person.ID
WHERE Person.Group_ID= '1' AND Person.Active = 'true' 
AND Saldo1.Year = '2017' AND Saldo1.Timekonto = '4'
AND Saldo2.Timekonto='52') Table1 LEFT OUTER JOIN
Table2 on Table2.Person_ID=Table1.Name