我正在尝试将一个人的前三行的值全部显示在一行中.

I am trying to get the values of the top three rows for a person to all display in one row.

我的数据如下所示:

id       co_number  client_no  Client_name  taken_date    taken_value
--------------------------------------------------------------------------
270103   12         1111       John Doe     6/7/11 8:45 AM    108
270100   12         1111       John Doe     5/3/11 10:49 AM   109
270097   12         1111       John Doe     4/4/11 1:58 PM    109
270094   12         1111       John Doe     3/1/11 9:04 AM    106
270091   12         1111       John Doe     2/1/11 8:47 AM    105
270088   12         1111       John Doe     1/4/11 9:10 AM    106
270120   12       2222       Jane Smith    6/7/11 9:06 AM     215
270117   12       2222       Jane Smith    5/3/11 2:01 PM     216
270114   12       2222       Jane Smith   4/4/11 2:08 PM      214
270111   12       2222       Jane Smith    3/1/11 9:27 AM     209
270159   12       3333       John Adams    6/7/11 9:45 AM     205
270156   12       3333       John Adams   5/3/11 2:12 PM      203
270153   12       3333       John Adams    4/4/11 1:42 PM     202
270150   12       3333       John Adams   3/1/11 10:32 AM     198

我希望数据像这样显示(Date1 是最新的，然后是 Date2，然后是 Date3):

I want the data to display like this (Date1 being the most recent, then Date2, then Date3):

co#  Name      Date1             Value1 Date2             Value2 Date3             Value3
-------------------------------------------------------------------------------------------
12   John Doe  2011-06-07 08:45  108.0  2011-05-03 10:49  109.0  2011-04-04 13:58  109.0

这是我目前所拥有的.它可以工作，但速度很慢(需要 30 秒才能返回一个 co_number)，所以我想知道是否有更好更有效的方法.

Here is what I have so far. It works but it's slow (takes 30 secs to return one co_number) so I'm wondering if there is a better more efficient way of doing this.

select 
vmain.co_nmber, vmain.Client_name, vmain.Taken_date, vmain.Taken_value
, (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < vmain.Taken_date order by vdate.Taken_date desc) as date2
, (select top 1 Taken_value from vital v_value where v_value.co_nmber=vmain.co_nmber and v_value.Medical_Record_Number=vmain.Medical_Record_Number and v_value.Taken_date < vmain.Taken_date order by v_value.Taken_date desc) as value2
, (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < vmain.Taken_date order by vdate.Taken_date desc) order by vdate.Taken_date desc) as date3
, (select top 1 Taken_value from vital vvalue where vvalue.co_nmber=vmain.co_nmber and vvalue.Medical_Record_Number=vmain.Medical_Record_Number and vvalue.Taken_date < (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < vmain.Taken_date  order by vdate.Taken_date desc)  order by vvalue.Taken_date desc) as value3
from vital as vmain 
inner join(
SELECT v.co_nmber, v.Medical_Record_Number, max(v.Taken_date) as Taken_date
FROM Vital v
and v.co_nmber = 12
GROUP BY v.co_nmber, v.Medical_Record_Number 
) as vsub on vsub.co_nmber=vmain.co_nmber and vsub.Medical_Record_Number=vmain.Medical_Record_Number and vsub.Taken_date = vmain.Taken_date
and vmain.co_nmber = 12
order by vmain.co_nmber, vmain.Medical_Record_Number, vmain.Taken_date

帮助表示赞赏.

推荐答案

您可以使用 row_number 为每个公司和客户的记录编号.在此之后，您可以选择第一个，然后加入第二个和第三个.应该更快.

You could number your records per co and client with row_number. After this you can select the first ones and left join the second and third ones. Should be faster.

with cVital as (

select  v.co_nmber, v.Medical_Record_Number, v.Client_name,
        v.taken_date, v.taken_value,
        n = row_number() over (partition by v.co_nmber, v.Medical_Record_Number order by v.taken_date desc)
from    Vital v

)
select  [co#]=v1.co_nmber, [Name]=v1.Client_name,
        Date1 = v1.taken_date, Value1 = v1.taken_value,
        Date2 = v3.taken_date, Value2 = v2.taken_value,
        Date3 = v2.taken_date, Value3 = v3.taken_value
from    cVital v1
left join cVital v2
    on  v2.co_nmber = v1.co_nmber
    and v2.Medical_Record_Number = v1.Medical_Record_Number
    and v2.n = 2
left join cVital v3
    on  v3.co_nmber = v1.co_nmber
    and v3.Medical_Record_Number = v1.Medical_Record_Number
    and v3.n = 3
where   v1.n = 1
order by v1.co_nmber, v1.Medical_Record_Number;