求个SQL语句,在线等,急........

小飛不想飛 2017-01-05 02:19:35


说明:同一个SAID包含多个SBID,同一个SBID包含多个SCID,现在要查询每个SCID日期最大的一条数据所以信息,也就是背景标浅黄的数据,求教,在线等。
...全文
299 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
Ginnnnnnnn 2017-01-06
  • 打赏
  • 举报
回复
SELECT d.* FROM table a where not exists(select * from table1 WHERE SAID = a.SAID AND SCID = a.SCID and SBID = a.SBID AND SATime > a.SATime)
小飛不想飛 2017-01-06
  • 打赏
  • 举报
回复
引用 4 楼 Tiger_Zhao 的回复:
用(SAID,SCID,SATime)建一个索引。
建了索引,但看了一下SQL语句的执行计划,发现SQL语句已经用到了索引,但是优化却不明显。
Tiger_Zhao 2017-01-06
  • 打赏
  • 举报
回复
        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

试试这个。避免了大数据下的 DISTINCT。
小飛不想飛 2017-01-06
  • 打赏
  • 举报
回复
谢谢各位了,下班前结贴。
小飛不想飛 2017-01-06
  • 打赏
  • 举报
回复
引用 10 楼 AMo18650712895 的回复:
SELECT * FROM shuju A
WHERE A.SATime = (SELECT MAX(SATime) FROM shuju WHERE SAID = A.SAID AND SBID = A.SBID AND SCID = A.SCID)
ORDER BY A.SAID,A.SBID,A.SCID


还不错,数据也是秒出来
小飛不想飛 2017-01-06
  • 打赏
  • 举报
回复
引用 8 楼 ch21st 的回复:
这样速度怎样


SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY SAID,SBID,SCID ORDER BY SATime desc) AS rn FROM shuju) t WHERE t.rn=1


和1楼、7楼一样,130多万条数据最终查询出950条有用数据,查询结果都一样,你和7楼都是数据秒出来,但是你的性能应该更高一些。
AMo35 2017-01-06
  • 打赏
  • 举报
回复
SELECT * FROM shuju A WHERE A.SATime = (SELECT MAX(SATime) FROM shuju WHERE SAID = A.SAID AND SBID = A.SBID AND SCID = A.SCID) ORDER BY A.SAID,A.SBID,A.SCID
小飛不想飛 2017-01-06
  • 打赏
  • 举报
回复
引用 7 楼 KanzakiOrange 的回复:
SELECT d.*
FROM table a
where not exists(select * from table1 WHERE SAID = a.SAID
AND SCID = a.SCID
and SBID = a.SBID
AND SATime > a.SATime)


效率非常高,数据秒出来,索引利用了百分之38,谢谢了
道素 2017-01-06
  • 打赏
  • 举报
回复
这样速度怎样

SELECT * FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY SAID,SBID,SCID ORDER BY SATime desc) AS rn FROM shuju) t WHERE t.rn=1
  • 打赏
  • 举报
回复
1楼的方法 很巧妙啊
Tiger_Zhao 2017-01-05
  • 打赏
  • 举报
回复
用(SAID,SCID,SATime)建一个索引。
小飛不想飛 2017-01-05
  • 打赏
  • 举报
回复
引用 1 楼 Tiger_Zhao 的回复:
        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
好像是可以的,但是因为表里面有130多万条数据,查询花了八九秒,最后查询结果有950条,有效率高一点的办法吗
小飛不想飛 2017-01-05
  • 打赏
  • 举报
回复

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')


Tiger_Zhao 2017-01-05
  • 打赏
  • 举报
回复
        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

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧