我有下表
recordID createdDate ForeignKeyID
00QA000000PtFXaMAN 2012-01-03 13:23:36.000 001A000000ngM21IAE
00QA000000OS2QiMAL 2011-12-15 12:03:02.000 001A000000ngM21IAE
.
.
.
.
我正在尝试获取foreignKeyID的recordID,其中createdDAte是foreignKeyID的min(createdDate)
如果 recordID 是 Identity int 我可以通过执行以下查询来获取
Select min(recordId),ForeignkeyID
from table
group by ForeignKeyId
我原本以为我可以使用以下查询创建临时表,然后将其连接到 minDate 和foreignKeyID 上的表,但后来我发现foreignKeyId 有多个记录具有完全相同的createdDate。
Select min(createdDate) as minDate,ForeignKeyID
from table
group by ForeignKeyId
我愿意使用临时表或子查询或其他任何东西。谢谢。
其中一种方法是
select A.ForeignKeyID, R.recordID
from (select distinct t.ForeignKeyID from table as t) as A
outer apply
(
select top 1 t.recordID
from table as t where t.ForeignKeyID = A.ForeignKeyID
order by t.createdDate asc
) as R
SQL 小提琴示例
另一种方法是
select top 1 with ties
t.recordID, t.ForeignKeyID
from table as t
order by row_number() over (partition by t.ForeignKeyID order by t.createdDate)
SQL 小提琴示例
还有另一种方式
select A.recordID, A.ForeignKeyID
from
(
select
t.recordID, t.ForeignKeyID,
row_number() over (partition by t.ForeignKeyID order by t.createdDate) as RowNum
from table1 as t
) as A
where A.RowNum = 1
SQL 小提琴示例
由于代码较短,我比其他人更喜欢第二个
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)