如何获得记录的记录ID以及每个外键的最短日期？

问题描述

我有以下表格：

  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） p>

如果recordID是标识int我可以通过执行以下查询来获得：

 从表
中选择min（recordId），ForeignkeyID 
 by ForeignKeyId 
  

我原本以为我可以用下面的查询创建临时表，然后将它加入到minDate和foreignKeyID的表中，但后来我发现foreignKeyId的多个记录具有相同的createdDate。

 选择min（createdDate）作为minDate，ForeignKeyID 
从表
通过ForeignKeyId获取
  

我打开使用临时表或子查询或其他任何东西。感谢。

解决方案

<$ p
$（b
）从表中选择A.ForeignKeyID，R.recordID
（从表中选择不同的t.ForeignKeyID）作为A
外部申请
（
选择top 1 t.recordID
from table as t其中t.ForeignKeyID = A.ForeignKeyID
order by t.createdDate asc
）as R

另一种方法是

pre code> select top 1 with tie
t.recordID，t.ForeignKeyID
from table as t
order by row_number（）over（分区由t.ForeignKeyID order by t.createdDate）

另一种方式

 选择A.recordID，A.ForeignKeyID 
从
（
选择
 t.recordID，t.ForeignKeyID，
 row_number（）over（由t.ForeignKeyID order by t.createdDate分区）作为RowNum 
从表1中作为t 
）作为A 
其中A.RowNum = 1 
  

由于代码简洁，我喜欢比其他人多一些。

I have the following table

recordID               createdDate                         ForeignKeyID
00QA000000PtFXaMAN     2012-01-03 13:23:36.000             001A000000ngM21IAE
00QA000000OS2QiMAL     2011-12-15 12:03:02.000             001A000000ngM21IAE
.
.
.
.

I am trying to get the recordID for foreignKeyID where createdDAte is the min(createdDate) for foreignKeyID

if recordID is identity int I can get that by doing the following query

Select min(recordId),ForeignkeyID
from table 
group by ForeignKeyId

I originaly thought that I can create temp table with the following query and then joining it to table on minDate and foreignKeyID but then I found out that there are multiple records for foreignKeyId that has the same exact createdDate.

Select min(createdDate) as minDate,ForeignKeyID
from table
group by ForeignKeyId

I'm open with using temp table or subquery or anything really. Thanks.

解决方案

One of the ways to do it is

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 FIDDLE EXAMPLE

Another way to do it is

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 FIDDLE EXAMPLE

And another way

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 FIDDLE EXAMPLE

I like second one more than others because of shortness of code

这篇关于如何获得记录的记录ID以及每个外键的最短日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！