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

2017-01-05 02:19:35 422 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

手把手教你为ArduPilot添加自定义飞行模式：以自动巡检航线为例本文详细介绍了如何为开源飞控ArduPilot添加自定义飞行模式，并以自动巡检航线为例，提供了从状态机设计、代码实现到地面站集成的完整开发指南。通过七步法手把手教学，帮助开发者深入理解飞控核心逻辑，实现无人机自主执行复杂巡检任务，满足行业应用定制需求。阅读详情

说明：同一个SAID包含多个SBID，同一个SBID包含多个SCID，现在要查询每个SCID日期最大的一条数据所以信息，也就是背景标浅黄的数据，求教，在线等。

...全文

debian linux系统安装教程,Debian 10（Buster）安装过程图文详解 Debian 项目发布了它的最新稳定版 Debian 10，其代号是 “Buster”，这个发布版将获得 5 年的支持。Debian 10 可用于 32 位和 64 位系统。这个发布版带来很多新的特色，列出下面一些特色：引入新的 Debian 10 的主题 “FuturePrototype”新版本的电脑桌面环境 GNOME 3.30、Cinnamon 3.8、KDE Plasma 5.14、MAT... 阅读详情

小飛不想飛

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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

学海无涯-回头是岸 2017-01-05

打赏
举报

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

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

相关推荐