选择所有列的顶部/底部和位置编号Order By [英] Selecting Top/Last and Position number Order By with all columns

问题描述

关注该问题选择第一个N每个组按日期排序的行

USE [Test]

CREATE TABLE [dbo].[Masters] 
(
    [MasterId] [nchar](36) NOT NULL PRIMARY KEY,
    [Tags] [nchar](100) NULL,
    [Numbers] [int] NOT NULL
);

CREATE TABLE [dbo].[Details] 
(
    [DetailId] [nchar](36) NOT NULL PRIMARY KEY,
    [MasterId] [nchar](36) FOREIGN KEY REFERENCES Masters(MasterId),
    [Date_Time] [datetime2](7) NOT NULL,
    [Value] [int] NOT NULL
);


INSERT INTO Masters (MasterId, Tags, Numbers) VALUES ('M0', 'Tag0,Tag1', 6);
INSERT INTO Masters (MasterId, Tags, Numbers) VALUES ('M1', 'Tag1,Tag2', 5);
INSERT INTO Masters (MasterId, Tags, Numbers) VALUES ('M2', 'Tag0,Tag2', 6);

INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D0', 'M0', '20190101 00:30:00 AM', 0);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D1', 'M0', '20200101 11:00:00 AM', 1);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D2', 'M0', '20200701 01:00:00 AM', 2);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D3', 'M0', '20210715 10:00:00 AM', 3);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D4', 'M0', '20210715 11:30:00 AM', 4);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D5', 'M0', '20210715 11:00:00 AM', 5);

INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D10', 'M1', '20190101 00:20:00 AM', 6);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D11', 'M1', '20200101 01:00:00 AM', 7);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D12', 'M1', '20200701 09:00:00 AM', 8);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D13', 'M1', '20210101 10:00:00 AM', 9);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D14', 'M1', '20210701 10:00:00 AM', 10);

INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D20', 'M2', '20190101 00:00:00 AM', 11);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D21', 'M2', '20190101 01:30:00 AM', 12);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D22', 'M2', '20200101 01:30:00 AM', 13);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D23', 'M2', '20200701 08:30:00 AM', 14);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D24', 'M2', '20210101 01:30:00 AM', 15);
INSERT INTO Details (DetailId, MasterId, Date_Time, Value) VALUES ('D25', 'M2', '20210701 01:30:00 AM', 16);

现在这里是一个简单的输出

Now here a simple output

SELECT d.* 
FROM Details AS d 
ORDER BY d.Date_Time DESC;

输出:

D4  M0  2021-07-15 11:30:00.0000000 4
D5  M0  2021-07-15 11:00:00.0000000 5
D3  M0  2021-07-15 10:00:00.0000000 3
D14 M1  2021-07-01 10:00:00.0000000 10
D25 M2  2021-07-01 01:30:00.0000000 16
D13 M1  2021-01-01 10:00:00.0000000 9
D24 M2  2021-01-01 01:30:00.0000000 15
D12 M1  2020-07-01 09:00:00.0000000 8
D23 M2  2020-07-01 08:30:00.0000000 14
D2  M0  2020-07-01 01:00:00.0000000 2
D1  M0  2020-01-01 11:00:00.0000000 1
D22 M2  2020-01-01 01:30:00.0000000 13
D11 M1  2020-01-01 01:00:00.0000000 7
D21 M2  2019-01-01 01:30:00.0000000 12
D0  M0  2019-01-01 00:30:00.0000000 0
D10 M1  2019-01-01 00:20:00.0000000 6
D20 M2  2019-01-01 00:00:00.0000000 11

现在，我希望最后一行包括它的位置(13、14、15、16和17).

Now, I would like the last rows including its position (13, 14, 15, 16 an 17).

13  D11 M1  2020-01-01 01:00:00.0000000 7
14  D21 M2  2019-01-01 01:30:00.0000000 12
15  D0  M0  2019-01-01 00:30:00.0000000 0
16  D10 M1  2019-01-01 00:20:00.0000000 6
17  D20 M2  2019-01-01 00:00:00.0000000 11

该行与排名行的查询是什么?

What is the query for that rows with the position row?

我正在尝试(我知道是不好的！)

I was trying with (I know is bad!)

SELECT TOP(5) 
    COUNT(Date_Time), * 
FROM 
    Details 
GROUP BY 
    COUNT(Date_Time) 
ORDER BY 
    COUNT(Date_Time) DESC;

推荐答案

检查方式:

SELECT * FROM
    (SELECT Top(5) ROW_NUMBER() OVER(ORDER BY d.Date_Time DESC) AS position, d.* 
        FROM Details AS d 
        ORDER BY d.Date_Time ASC
    ) AS d2 ORDER BY d2.Date_Time DESC;

这篇关于选择所有列的顶部/底部和位置编号Order By的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！