根据条件从每组中排第一行

Id Date
1 5/11/2015
1 5/11/2015
1 5/12/2015
1 5/13/2015
2 5/11/2015
2 5/11/2015
2 5/12/2015
2 5/13/2015
3 5/14/2015
3 5/15/2015
3 5/16/2015
3 5/17/2015
4 5/13/2015
4 5/13/2015
4 5/14/2015
4 5/15/2015


ID Name
1 Roy
2 Jame
3 Jani
4 Romi

I am not able to get the first row matching with second table

我无法获得与第二个表匹配的第一行

I want to get only one row from each table group by ID where date is greater than today's date (i.e. 5/11/2015), like as shown below.

我想通过ID获取每个表组中只有一行,其中日期大于今天的日期(即2015年11月5日),如下所示。

Id Name Date
1 Roy 5/12/2015
2 Jane 5/12/2015
3 Jani 5/14/2015
4 Romi 5/13/2015

4 个解决方案

#1

Use cross apply or correlated subquery to do this

使用交叉应用或相关子查询来执行此操作

select * from Table2 t2
cross apply 
(select top 1 [date] from table1 t1
where t2.id = t1.id
AND t1.[date] > convert(date,getdate())
ORDER BY [date] ASC) CS

SQLFIDDLE DEMO

#2

One option would be to use row_number():

一种选择是使用row_number():

with cte as (
    select t2.id, t2.name, t1.date, 
           row_number() over (partition by t2.id order by t1.date) rn
    from table1 t1
        join table2 t2 on t1.id = t2.id 
    where t1.date > getdate())
select id, name, date
from cte
where rn = 1

SQL Fiddle Demo

SQL小提琴演示

#3

You can use a CTE + ROW_NUMBER:

您可以使用CTE + ROW_NUMBER:

WITH CTE AS
(
    SELECT t1.ID,
       t1.[Date],
       t2.Name,
       RN = ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.[Date] ASC)
   FROM dbo.Table1 t1
   INNER JOIN  dbo.Table2 t2
     ON t1.ID = t2.ID
   WHERE t1.[Date] > GetDate()
)
SELECT ID, Name, Date
FROM CTE
WHERE RN = 1

Demo

The ORDER BY t1.[Date] ASC specifies which row you want to keep, in this case the row with the oldest date, use DESC if you want to keep the newest.

ORDER BY t1。[Date] ASC指定要保留的行,在这种情况下是具有最早日期的行,如果要保留最新的,则使用DESC。

#4

Use a derived table with GROUP BY to get each id's lowest date (after today). Then JOIN with t2:

使用带有GROUP BY的派生表来获取每个id的最低日期(今天之后)。然后用t2加入:

select t1.id, t2.name, t1.date
from (select Id, min(Date)
      from table1
      where Date > getdate()
      group by id) t1 (id, date) join table 2 t2 on t1.id = t2.id

#1