22,199
社区成员
发帖
与我相关
我的任务
分享
SELECT d.*
FROM ( SELECT SAID,SCID
FROM table1
GROUP BY SAID,SCID
) m
CROSS APPLY (
SELECT TOP 1 *
FROM table1
WHERE SAID = m.SAID
AND SCID = m.SCID
ORDER BY SATime DESC
) d
SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY SAID,SBID,SCID ORDER BY SATime desc) AS rn FROM shuju) t WHERE t.rn=1
create table shuju(
id uniqueidentifier primary key not null,
SAID uniqueidentifier not null,
SBID uniqueidentifier not null,
SCID uniqueidentifier not null,
SATime datetime not null,
SAValue int not null,
SBValue int not null,
SCValue int not null,
Unit nvarchar(10) not null,
Status bit not null
)
insert into shuju values(NEWID(),'A7A4F17A-02D1-4C67-A27D-1868B255990A','C2A3F690-1806-462A-948C-0757657AF2B6','E5E485D3-489D-4DF9-AF5B-7F50E7B2F34C','2016-12-16 10:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'A7A4F17A-02D1-4C67-A27D-1868B255990A','C2A3F690-1806-462A-948C-0757657AF2B6','E5E485D3-489D-4DF9-AF5B-7F50E7B2F34C','2016-12-08 10:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'A7A4F17A-02D1-4C67-A27D-1868B255990A','C2A3F690-1806-462A-948C-0757657AF2B6','E5E485D3-489D-4DF9-AF5B-7F50E7B2F34C','2016-12-02 10:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'A7A4F17A-02D1-4C67-A27D-1868B255990A','C2A3F690-1806-462A-948C-0757657AF2B6','8BDC1623-6E2C-4965-A19D-4B13F9EDAAF9','2016-11-26 10:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'A7A4F17A-02D1-4C67-A27D-1868B255990A','C2A3F690-1806-462A-948C-0757657AF2B6','8BDC1623-6E2C-4965-A19D-4B13F9EDAAF9','2015-10-28 10:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'A7A4F17A-02D1-4C67-A27D-1868B255990A','C2A3F690-1806-462A-948C-0757657AF2B6','8BDC1623-6E2C-4965-A19D-4B13F9EDAAF9','2015-10-27 10:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','615EA477-300D-4B49-BCBE-F3D67156B5FC','2016-12-08 10:00:00.000',0.6,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','615EA477-300D-4B49-BCBE-F3D67156B5FC','2016-11-24 10:00:00.000',0.6,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','615EA477-300D-4B49-BCBE-F3D67156B5FC','2016-11-22 10:00:00.000',0.6,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','615EA477-300D-4B49-BCBE-F3D67156B5FC','2015-11-19 09:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','5CA94AAA-2F19-4B85-B87A-B66044716BDB','2015-11-11 09:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','5CA94AAA-2F19-4B85-B87A-B66044716BDB','2015-11-06 09:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','5CA94AAA-2F19-4B85-B87A-B66044716BDB','2015-11-05 09:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','30F9B289-2733-4A54-9039-26CA1FE59303','5CA94AAA-2F19-4B85-B87A-B66044716BDB','2015-11-04 09:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','B0793552-DEBE-48DD-AB0E-6D96752FEB99','94CC315B-092B-4225-B6BE-54253518BCF1','2015-11-02 11:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','B0793552-DEBE-48DD-AB0E-6D96752FEB99','94CC315B-092B-4225-B6BE-54253518BCF1','2015-10-31 09:00:00.000',0,0,0,'mm','0')
insert into shuju values(NEWID(),'AFF3F921-3AAC-4552-8BD9-224AAD2C3E81','B0793552-DEBE-48DD-AB0E-6D96752FEB99','94CC315B-092B-4225-B6BE-54253518BCF1','2015-10-30 09:00:00.000',0,0,0,'mm','0')
SELECT d.*
FROM (SELECT DISTINCT SAID,SCID
FROM table1
) m
CROSS APPLY (
SELECT TOP 1 *
FROM table1
WHERE SAID = m.SAID
AND SCID = m.SCID
ORDER BY SATime DESC
) d