sql - 获取最大值(值)而不在 SQL Server 中分组

Question

我有这个不需要的分组问题。

这是我的 SQL 代码，用于创建表并为其设置种子:

create table player 
(
    playerid int identity(1,1) primary key,
    Name varchar(255)
)

create table gameplay
(
    gameplayid int identity(1,1) primary key,
    Name varchar(255),
    playerid int references player(playerid)
)

Create table room
(
    Roomid int identity(1,1) primary key,
    number int
)

Create table PC
(
    PCid int identity(1,1) primary key,
    Name varchar(255)
)

Create table playperiod
(
    PKid int identity(1,1) primary key,
    StartDate Datetime null,
    EndDate Datetime null,
    Roomid int references room(roomid),
    PCID int references PC(PCid),
    gameplayid int references gameplay(gameplayid)
)

Insert into player (name) values ('Kris')
Insert into player (name) values ('Bart')
Insert into player (name) values ('Bob')
Insert into player (name) values ('John')
Insert into player (name) values ('Iris')

insert into room (number) values (1000)
insert into room (number) values (1001)
insert into room (number) values (1002)
insert into room (number) values (1003)
insert into room (number) values (1004)
insert into room (number) values (1005)
insert into room (number) values (1006)
insert into room (number) values (1007)
insert into room (number) values (1008)
insert into room (number) values (1009)
insert into room (number) values (1010)

insert into gameplay (name, playerid) values ('WOW', 1)
insert into gameplay (name, playerid) values ('LOL', 2)
insert into gameplay (name, playerid) values ('DIablo', 3)
insert into gameplay (name, playerid) values ('Starcraft', 4)
insert into gameplay (name, playerid) values ('Borderlands', 5)

insert into PC (name) values ('Dell (1)')
insert into PC (name) values ('HP (1)')
insert into PC (name) values ('Dell (2)')
insert into PC (name) values ('HP (2)')
insert into PC (name) values ('Dell (3)')
insert into PC (name) values ('HP (3)')
insert into PC (name) values ('Dell (4)')
insert into PC (name) values ('HP (4)')
insert into PC (name) values ('Dell (5)')
insert into PC (name) values ('HP (5)')
insert into PC (name) values ('Dell (6)')
insert into PC (name) values ('HP (6)')
insert into PC (name) values ('Dell (7)')
insert into PC (name) values ('HP (7)')
insert into PC (name) values ('Dell (8)')
insert into PC (name) values ('HP (8)')
insert into PC (name) values ('Dell (9)')
insert into PC (name) values ('HP (9)')
insert into PC (name) values ('Dell (10)')
insert into PC (name) values ('HP (10)')

truncate table playperiod

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-22 10:02:00.000', '2015-01-22 11:30:00.000', 1, 1, 1)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid)   
values ('2015-01-22 10:02:00.000', '2015-01-22 16:02:00.000', 1, 2, 2)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-22 10:04:00.000', '2015-01-28 10:02:00.000', 2, 3, 3)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-20 10:02:00.000', '2015-01-22 10:02:00.000', 2, 4, 4)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-22 22:40:00.000', '2015-01-22 22:50:00.000', 3, 5, 1)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-22 22:55:00.000', '2015-01-22 23:50:00.000', 3, 6, 1)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-22 11:30:00.000', '2015-01-22 13:30:00.000', 1, 1, 1)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-22 14:30:00.000', '2015-01-22 16:30:00.000', 1, 1, 1)

insert into playperiod (StartDate, EndDate, Roomid, PCID, gameplayid) 
values ('2015-01-22 19:30:00.000', '2015-01-22 22:30:00.000', 1, 1, 1)

知道这些是我的查询:

第 1 步)获得 1 天的所有内容(目前没问题)

Select startdate, number, pc.name, gp.Name,p.Name  
from playperiod PD
left join room R on R.Roomid = PD.Roomid
left join pc on pc.PCid = PD.PCID
left join gameplay gp on GP.gameplayid = PD.gameplayid
left join player P on P.playerid = GP.playerid 
where 
DATEDIFF(day, pd.startdate, '2015-01-22') >= 0 and DATEDIFF(day, pd.startdate, '2015-01-22') <= 0

第 2 步)从开始日期获取最大日期值(问题站)

Select max(startdate) as startdate, number, pc.name, gp.Name,p.Name  
from playperiod PD
left join room R on R.Roomid = PD.Roomid
left join pc on pc.PCid = PD.PCID
left join gameplay gp on GP.gameplayid = PD.gameplayid
left join player P on P.playerid = GP.playerid 
where 
DATEDIFF(day, pd.startdate, '2015-01-22') >= 0 and DATEDIFF(day, pd.startdate, '2015-01-22') <= 0
group by number, pc.name, gp.Name,p.Name

最后我做了分组，所以行为是正确的。但在这里，用户“Kris”当天在 2 个房间的 3 台电脑上玩游戏。

但我想有这样的情况:

• 只有该房间号/pc/游戏/名称的最大开始日期(对于该行)
• 我只需要最新的情况。 (即使他换了电脑或房间)

这是我需要的结果:

谁能帮帮我？

Answer 1

您可以阅读 row_number。基本上它根据一些标准分配号码。在这里，我说要为 gp.Name、P.Name 的每个组组合进行编号，然后我按日期时间对这些组进行排序并取最新的一个。

SELECT
    startdate ,
    number ,
    pc_name ,
    gp_name ,
    Name
FROM
    (
      SELECT
        startdate ,
        number ,
        pc.name pc_name ,
        gp.Name gp_name ,
        P.Name ,
        ROW_NUMBER() OVER ( PARTITION BY gp.Name, P.Name ORDER BY startdate DESC ) rn
      FROM
        playperiod PD
        LEFT JOIN room R ON R.Roomid = PD.Roomid
        LEFT JOIN pc ON pc.PCid = PD.PCID
        LEFT JOIN gameplay gp ON gp.gameplayid = PD.gameplayid
        LEFT JOIN player P ON P.playerid = gp.playerid
      WHERE
            DATEDIFF(DAY, PD.startdate, '2015-01-22') >= 0
        AND DATEDIFF(DAY, PD.startdate, '2015-01-22') <= 0
    ) a
WHERE
    rn = 1