Блог

Итак, у меня есть две таблицы:

 #ProjectHealthReports
Id | From       | SubmittedOn
1  | 2020-01-01 |
2  | 2020-02-01 | 2020-10-23
3  | 2020-03-01 |
4  | 2020-04-01 | 2020-10-23
5  | 2020-05-01 | 2020-10-23


#ProjectHealthReportItems
Id | Note                                      | ProjectHealthReportId
1  | First for 2020-01-01                      | 1
2  | Second for 2020-01-01                     | 1
3  | First for 2020-02-01                      | 2
4  | Second for 2020-02-01                     | 2
5  | First for 2020-03-01                      | 3
6  | Second for 2020-03-01                     | 3
7  | First for 2020-04-01                      | 4
8  | Second for 2020-04-01                     | 4
9  | (We want this one) First for 2020-05-01   | 5
10 | (We want this one) Second for 2020-05-01  | 5
  

Как я могу получить все #ProjectHealthReportItems и #ProjectHealthReport подробности для последней From даты, которая имеет значение для SubmittedOn (так что в данном случае это будут ProjectHealthReport 5 и ProjectHealthReportItems 9, 10).

В принципе, мне нужно что-то вроде этого, очевидно, без top 1 , поскольку он возвращает только одну строку, и в этом случае мне нужно вернуть 2 строки 🙂

 select top 1 phr.Id, phr.[From], phr.SubmittedOn, phri.Note from #ProjectHealthReports phr
inner join #ProjectHealthReportItems phri on phr.Id = phri.ProjectHealthReportId
where phr.SubmittedOn is not null
order by phr.[From] desc
  

Вот SQL для создания и заполнения таблиц

 create table #ProjectHealthReports(
    Id int primary key,
    [From] date not null ,
    SubmittedOn date null
)
go
create table #ProjectHealthReportItems(
    Id int primary key,
    Note nvarchar(max),
    ProjectHealthReportId int constraint FK_PHR references #ProjectHealthReports
)
go

insert into #ProjectHealthReports(Id, [From], SubmittedOn)
values (1, '2020-01-01', null),
       (2, '2020-02-01', getutcdate()),
       (3, '2020-03-01', null),
       (4, '2020-04-01', getutcdate()),
       (5, '2020-05-01', getutcdate())
go

insert into #ProjectHealthReportItems(Id, Note, ProjectHealthReportId)
values (1, 'First for 2020-01-01', 1),
       (2, 'Second for 2020-01-01', 1),
       (3, 'First for 2020-02-01', 2),
       (4, 'Second for 2020-02-01', 2),
       (5, 'First for 2020-03-01', 3),
       (6, 'Second for 2020-03-01', 3),
       (7, 'First for 2020-04-01', 4),
       (8, 'Second for 2020-04-01', 4),
       (9, '(We want this one) First for 2020-05-01', 5),
       (10, '(We want this one) Second for 2020-05-01', 5)
go
  

Сначала выберите top затем join

    select t.*, phri.Note
   from (select top(1) phr.Id phrid, phr.[From], phr.SubmittedOn
         from #ProjectHealthReports phr
         where phr.SubmittedOn is not null
         order by phr.[From] desc) t
   inner join #ProjectHealthReportItems phri on t.phrId =   phri.ProjectHealthReportId
  

Я бы предложил оконные функции:

 select phr.*, phri.*
from #ProjectHealthReports phr left join
     (select phri.*,
             row_number() over (partition by ProjectHealthReportId order by id desc) as seqnum
      from #ProjectHealthReportItems phri
     ) phri
     on phr.Id = phri.ProjectHealthReportId and seqnum = 1
order by phr.[From] desc;
  

Вы также можете сделать это, используя фильтрацию в where , например, коррелированный подзапрос:

 select phr.*, phri.*
from #ProjectHealthReports phr join
     #ProjectHealthReportItems phri
     on phr.Id = phri.ProjectHealthReportId and seqnum = 1
where phri.id = (select max(phri2.id)
                 from #ProjectHealthReportItems phri2
                 where phri2.ProjectHealthReportId = phri.ProjectHealthReportId
                )
order by phr.[From] desc
  

Эффективным способом сделать это без объединения по ЛЕВОМУ КРАЮ было бы присвоить номер строки, используя ROW_NUMBER() оконную функцию, таблице #projectthealthreports . Что-то вроде этого

 with lv_cte as (
    select *, row_number() over (order by [From] desc) rn
    from #ProjectHealthReports)
select l.*, phri.* 
from lv_cte l
     join #ProjectHealthReportItems phri on l.id=phri.ProjectHealthReportId
where l.rn=1;

  

Вывод

 Id  From        SubmittedOn rn  Id  Note                              ProjectHealthReportId
5   2020-05-01  2020-10-23  1   9   (We want this one) First for 2020-05-01 5
5   2020-05-01  2020-10-23  1   10  (We want this one) Second for 2020-05-01    5