SQL Server获取前三个记录的值，并显示在每个人的一行中

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

我试图让一个人的前三行的值全部显示在一行中。

My data looks like this:

我的数据如下所示:

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

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

我希望数据显示如下(Date1是最新的,然后是Date2,然后是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

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.

这是我到目前为止所拥有的。它工作但它很慢(返回一个co_number需要30秒)所以我想知道是否有更好的更有效的方法来做到这一点。

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

Help appreciated.

4 个解决方案

#1

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.

您可以使用row_number为每个co和客户端编号记录。在此之后,您可以选择第一个,然后左连接第二个和第三个。应该更快。

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;

#2

The big question is "What is causing the query to run slowly?"

最大的问题是“导致查询运行缓慢的原因是什么?”

Is your table indexed appropriately?

您的表是否正确索引?

Perhaps you could remove one of your computed columns at a time and see how that effects performance.

也许你可以一次删除一个计算列,看看效果如何表现。

#3

The slowness may be to using correlated sub-queries in the Select statement. Remember that sub-queries will execute for every record in the table

缓慢可能是在Select语句中使用相关的子查询。请记住,子查询将针对表中的每条记录执行

If you are only going to have a fixed number of values returned using a Temp table (or Table variable) and the Row_Number() Over method will work:

如果您只使用Temp表(或Table变量)返回固定数量的值,并且Row_Number()Over方法将起作用:

SELECT ROW_NUMBER() OVER (PARTITION BY TheKey ORDER BY DateValue DESC) RowNum, TheKey, DateValue, OtherValue
INTO tmp
FROM SomeTable
WHERE .....

SELECT x1.TheKey, x1.DateValue date1, x1.OtherValue Value1, x2.DateValue Date2, x2.OtherValue Value2, x3.CreateDate Date3, x3.OtherValue Value3
FROM (SELECT * FROM #tmp WHERE rownum=1) x1
left JOIN (SELECT * FROM #tmp WHERE rownum=2) x2 ON x1.TheKey = x2.TheKey
left JOIN (SELECT * FROM #tmp WHERE rownum=3) x3 ON x1.TheKey = x3.TheKey

#4

Ok, I updated my answer. Here is a solution that will work based on the query I made earlier:

好的,我更新了我的答案。这是一个基于我之前的查询工作的解决方案:

    DECLARE @Name VARCHAR(100)
    DECLARE @TmpName VARCHAR(100)
    DECLARE @ID INT
    DECLARE @CoNum INT
    DECLARE @Date1 DATETIME
    DECLARE @Date2 DATETIME
    DECLARE @Date3 DATETIME
    DECLARE @Value1 INT
    DECLARE @Value2 INT
    DECLARE @Value3 INT
    DECLARE @IndexValue INT
    DECLARE @SetValue INT
    DECLARE @SetDate DATETIME

    CREATE TABLE #OutputTable (
        co_number INT,
        Client_Name VARCHAR(200),
        Date1 DATETIME,
        Value1 INT,
        Date2 DATETIME,
        Value2 INT,
        Date3 DATETIME,
        Value3 INT
    )

    SELECT DISTINCT Client_name INTO #TempVitalNames FROM vital ORDER BY Client_name

    WHILE (SELECT COUNT(*) FROM #TempVitalNames) > 0
    BEGIN
        SELECT @IndexValue = 0
        SELECT TOP(1) @TmpName = Client_name FROM #TempVitalNames

        SELECT TOP(3) * INTO #TempVital FROM vital WHERE Client_name = @TmpName ORDER BY taken_date DESC

        WHILE (@IndexValue < 3)
        BEGIN       
            SET @Name = (SELECT TOP 1 Client_name FROM #TempVital)
            SET @CoNum = (SELECT TOP 1 co_number FROM #TempVital)
            SELECT TOP 1 @ID = id FROM #TempVital
            SET @SetDate = (SELECT TOP 1 taken_date FROM #TempVital)
            SET @SetValue = (SELECT TOP 1 taken_value FROM #TempVital)
            DELETE FROM #TempVital WHERE id = @ID
            SET @Date1 = CASE WHEN @IndexValue = 0 THEN @SetDate ELSE @Date1 END
            SET @Date2 = CASE WHEN @IndexValue = 1 THEN @SetDate ELSE @Date2 END
            SET @Date3 = CASE WHEN @IndexValue = 2 THEN @SetDate ELSE @Date3 END
            SET @Value1 = CASE WHEN @IndexValue = 0 THEN @SetValue ELSE @Value1 END
            SET @Value2 = CASE WHEN @IndexValue = 1 THEN @SetValue ELSE @Value2 END
            SET @Value3 = CASE WHEN @IndexValue = 2 THEN @SetValue ELSE @Value3 END

            SELECT @IndexValue = @IndexValue + 1                
        END

        INSERT INTO #OutputTable (co_number, Client_Name, Date1, Value1, Date2, Value2, Date3, Value3)
        ( SELECT @CoNum, @Name, @Date1, @Value1, @Date2, @Value2, @Date3, @Value3 )     

        DELETE #TempVitalNames WHERE Client_name = @TmpName
        DROP TABlE #TempVital
    END

    DROP TABLE #TempVitalNames

    SELECT * FROM #OutputTable

    DROP TABLE #OutputTable

Ok Claudia. This will first pull all the unique names in the table into a temporary table. It will then iterate through each name. Inside the iteration loop, it will do what my last program will do and pull the needed dates from the top 3 names and insert them into another temporary table. After all the names have been processed, it will then dump the contents of the temporary table, which should be the output you are looking for. I ran this query on my system with the test data you provided and it executed in under 1 second. I know you probably have a lot more rows then I had to work with, but it seems to work pretty well. If you need anything else, let me know and I will modify it accordingly. You are free to add any kind of additional where clauses to the SELECT statements, if needed. You may also need to change some of the data types. I wasn't sure if your taken_value was integer or decimal or whatever. I went with INT, but you can change it to match your needs.

好克劳迪娅。这将首先将表中的所有唯一名称拉入临时表。然后它将遍历每个名称。在迭代循环中,它将执行我的上一个程序将执行的操作,并从前3个名称中提取所需日期,并将它们插入到另一个临时表中。处理完所有名称后,它将转储临时表的内容,该表应该是您要查找的输出。我使用您提供的测试数据在我的系统上运行此查询,并在1秒内执行。我知道你可能有更多的行然后我必须使用,但它似乎工作得很好。如果您还有其他需要,请告诉我,我会相应修改。如果需要,您可以随意向SELECT语句添加任何类型的where子句。您可能还需要更改某些数据类型。我不确定你的taken_value是整数还是小数或其他什么。我选择了INT,但您可以根据自己的需要进行更改。

#1