22,302
社区成员




--#1.你用LEFT JOIN后的结果,不是和 select * from CurrentData.dbo.vw_SettingAggregateId 查询出来的记录条数是一样的吗?
--如果每个组中只取一条的话,在下面的语句中用top(1)即可。
--#2.试一下下面SQL的效率
;WITH Temp AS
(
select * from CurrentData.dbo.vw_SettingAggregateId
)
select a.SecId, a.AsOfDate, b.ComponentSecId from
(SELECT a.SecId, a.AsOfDate FROM Temp a GROUP BY a.SecId,a.AsOfDate) a
outer apply
(select /*top(1)*/* from Temp where SecId=a.SecId and AsOfDate=a.AsOfDate) b
SELECT a.SecId,
a.AsOfDate,
b.ComponentSecId
FROM
(
SELECT a.SecId,
a.AsOfDate
FROM CurrentData.dbo.vw_SettingAggregateId a
GROUP BY a.SecId,a.AsOfDate
) a
LEFT OUTER JOIN CurrentData.dbo.vw_SettingAggregateId b
ON a.SecId=b.SecId
AND a.AsOfDate=b.AsOfDate