如何从左连接SQL Server获得不同的结果

Question

我的Data返回具有cd.Id2的多个结果的结果，我希望cd.Id2是不同的。如果数据结果有五个cd.Id2有相同的，我想只得到每个重复cd.Id2的第一个结果。

> SELECT Id
>        ,IsIncluded
>        ,LineId
>        ,LineType
>        ,LineNumber
>        ,LineDescription
>          ,SUM(table2.StartBalance) AS StartBalance
>          ,SUM(table1.MoneyIn) AS MoneyIn
>          ,SUM(t1.MoneyOut) AS MoneyOut
> FROM Data AS table1
> LEFT JOIN StartBalanceData table2  ON table1.Id2 = table2.Id

的评论:

相同ID的数据倍数(join on first)

StartBalanceData映射ID

> GROUP BY Id, IsIncluded, LineId, LineType, LineNumber, LineDescription

我尝试使用LIMIT 1。但是不支持。

期望结果图像

https://pasteboard.co/inNzvmtEI2ux.png

Answer 1

您可以使用只获取一行的OUTER APPLY:

SELECT Id
,IsIncluded
,LineId
,LineType
,LineNumber
,LineDescription
,SUM(x.StartBalance) AS StartBalance
,SUM(table1.MoneyIn) AS MoneyIn
,SUM(t1.MoneyOut) AS MoneyOut
FROM Data AS table1
OUTER APPLY (
SELECT TOP 1 table2.StartBalance
FROM StartBalanceData table2 
WHERE table1.Id2 = table2.Id
ORDER BY <SOMETHING???>
) x

由于您提供了足够的帮助，避免了您的问题与诸如testdata或关于您希望最好获取的平衡数据之类的细节混淆，因此很难编写完整的查询，但也许这可能会让您开始