SQL Server获取前三条记录的值,并每人显示在一行中 [英] SQL Server Get values of top three records and display in one row per person
问题描述
我正在尝试获取一个人的前三行的值,以使其全部显示在一行中.
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
这是我到目前为止所拥有的.它可以工作,但是很慢(返回一个co_number需要30秒),所以我想知道是否有更好的更有效的方法.
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;
这篇关于SQL Server获取前三条记录的值,并每人显示在一行中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!