在Inner Join中是不同的

Question

我正在研究一些SQL存储过程。在SP中，我现在正在工作，我必须通过从其他表中获取一些数据来填充表。我的问题是，在这个有内部连接的选择中，我需要一个Distinct只应用于一个内部连接:

SELECT pss.ProgramScoreSummaryID
, vd.ProgramId, jou.TwAccountId, jou.DeviceId, psv.ScoreVersionID
, COUNT(jou.JourneyId) AS JourneyCount
, SUM(jou.Distance) AS TotalMileage
, SUM(jou.Duration) AS TotalDuration
FROM dbo.Program AS pro
INNER JOIN [jrn].[ProgramScoreSummary] AS pss ON pro.ProgramId = pss.ProgramId
INNER JOIN [dbo].[ProgramScoreVersion] AS psv ON pro.ProgramId = psv.ProgramID
AND pss.ScoreVersionID = psv.ScoreVersionID         
INNER JOIN [jrn].[Journey] AS jou ON jou.TwAccountId = vd.TwAccountId
AND dev.DeviceId = jou.DeviceId             
GROUP BY pss.ProgramScoreSummaryID

我如何插入一个不同的，使[旅程]表不采取重复的数据?

Answer 1

使用子查询将Journey表转换成您想要的样子。

对于每一个不同的行，都有一堆行对应于这一行。难点在于从每一组中选出一行作为这一组的代表。如果您只需要不同的键，或者只需要像MIN或MAX这样的简单聚合，那么这很容易。对于更复杂的选择代表的方法，您需要使用ROW_NUMBER() OVER (PARTITION BY ...)

例如:

...
INNER JOIN (
SELECT TwAccountId, DeviceId,
SUM(Distance) AS Distance,
SUM(Duration) AS Duration
FROM Journey
GROUP BY TwAccountId, DeviceId
) AS jou
ON jou.TwAccountId = vd.TwAccountId AND dev.DeviceId = jou.DeviceId

通常在子查询中进行分组，然后连接到其他东西比连接到组更容易。

Answer 2

感谢之前的答案，我能够找到一个解决方案:我所做的是使用With来选择我使用的函数ROW_NUMBER () OVER (PARTITION BY ...)方法如下:

WITH TEST AS 
(
select * from (select *, row_number() over (partition by Filename order by 
JourneyId) as row_number from [jrn].[Journey] ) as rows 
where row_number = 1
)

随后，在主选择中，我召回了上面写的选择，因此Journey表变成了Test，由于rownumber函数没有相同文件名的重复项:

SELECT pss.ProgramScoreSummaryID
, vd.ProgramId, jou.TwAccountId, jou.DeviceId, psv.ScoreVersionID
, COUNT(jou.JourneyId) AS JourneyCount
, SUM(jou.Distance) AS TotalMileage
, SUM(jou.Duration) AS TotalDuration
FROM dbo.Program AS pro
INNER JOIN [jrn].[ProgramScoreSummary] AS pss ON pro.ProgramId = pss.ProgramId
INNER JOIN [dbo].[ProgramScoreVersion] AS psv ON pro.ProgramId = psv.ProgramID
AND pss.ScoreVersionID = psv.ScoreVersionID         
INNER JOIN TEST AS jou ON jou.TwAccountId = vd.TwAccountId
AND dev.DeviceId = jou.DeviceId             
GROUP BY pss.ProgramScoreSummaryID

我希望这会对那些发现我同样问题的人有所帮助