SQL Server 获取前三条记录的值，每人一行显示
我正在尝试将一个人的前三行的值全部显示在一行中.
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;
相关文章