这两道题用SQL该怎么写？

liujuanjuan49 2016-10-30 12:42:43

...全文

572 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

baidu_35289351 2016-10-31

打赏
举报

比你3楼的效率呢？

中国风 2016-10-31

打赏
举报

引用 3 楼 baidu_35289351 的回复:

--1 -- roy_88斑竹，我2000的，用下面的也可以啊，是不是效率比你的低啊。 select a.Uid,a.Lid,a.Cdt,(select max(Cdt) from #tb where Uid=a.Uid and Cdt<a.Cdt)Pdt from #tb a

如果SQL2000只是显示一列，建议用TOP 1，通常效率会高一些，多数情况下聚合通常会比TOP 1效率低 e.g.

select a.Uid,a.Lid,a.Cdt,(select TOP 1 Cdt from #tb where Uid=a.Uid and Cdt<a.Cdt)Pdt from #tb a

baidu_35289351 2016-10-31

打赏
举报

baidu_35289351 2016-10-31

打赏
举报

--1 -- roy_88斑竹，我2000的，用下面的也可以啊，是不是效率比你的低啊。 if object_id('tempdb..#tb') is not null drop table #tb go create table #tb(Uid int,Lid int ,Cdt datetime) insert into #tb select 1,1001,'2016-10-01' union all select 1,1002,'2016-10-02' union all select 1,1003,'2016-10-03' union all select 1,1004,'2016-10-04' union all select 2,1005,'2016-10-05' union all select 2,1006,'2016-10-06' union all select 2,1007,'2016-10-07' union all select 2,1008,'2016-10-08' union all select 2,1009,'2016-10-09' union all select 3,1010,'2016-10-10' union all select 3,1011,'2016-10-11' union all select 3,1012,'2016-10-12' union all select 3,1013,'2016-10-13' select * from #tb select a.Uid,a.Lid,a.Cdt,(select max(Cdt) from #tb where Uid=a.Uid and Cdt<a.Cdt)Pdt from #tb a

中国风 2016-10-31

打赏
举报

按#3方法，用 TOP要加上 ORDER BY 情况下,索引要倒序估计才能体现效率 TOP 可针对性建索引，效率才会高些，数据量不大时影响相比较小 SQL2000:Top 1/Max 性能相差无几，Top能利用到排序时用Top 比如：如果SQL 05以上版本用推荐以下两种方法应该更有效率

CREATE INDEX IX_#tb ON #tb(Uid,Cdt DESC)
SELECT * FROM #tb AS a OUTER APPLY(SELECT TOP 1 Cdt FROM #tb WHERE Uid=a.Uid AND Cdt<=a.Cdt ORDER BY Cdt DESC) AS b
SELECT * FROM #tb AS a CROSS APPLY(SELECT MAX(Cdt) AS Cdt FROM #tb WHERE Uid=a.Uid AND Cdt<=a.Cdt) AS b

baidu_35289351 2016-10-31

打赏
举报

--是用TOP 1 快很多，我测试99W条记录，在没索引的情况下，用MAX(),费时2分56秒，而TOP 1则要3分58秒， --但我加了索引后，情况逆转，用MAX()费时50秒，大幅提升，但用TOP 1,则提升更加高，才17秒。 if object_id('tempdb..#tb') is not null drop table #tb go create table #tb(Uid int,Lid int ,Cdt datetime) declare @dt datetime declare @ii int declare @jj int declare @kk int set @jj=10001 set @ii=1 set @kk=1 set @dt='2016-09-01' while @ii<1000 begin while @kk<1000 begin insert into #tb values(@ii,@jj,@dt) set @jj=@jj+1 set @kk=@kk+1 set @dt=dateadd(s,10,@dt) end set @ii=@ii+1 set @kk=1 end --没索引，99W记录，用时2分56秒 select a.Uid,a.Lid,a.Cdt,(select Max(Cdt) from #tb where Uid=a.Uid and Cdt<a.Cdt)Pdt from #tb a --没索引，99W记录，用时3分58秒 select a.Uid,a.Lid,a.Cdt,(select TOP 1 Cdt from #tb where Uid=a.Uid and Cdt<a.Cdt order by Cdt desc)Pdt from #tb a --加索引后 create index tb_Uid_Cdt on #tb(Uid,Cdt) --加索引，99W记录，用时50秒 select a.Uid,a.Lid,a.Cdt,(select Max(Cdt) from #tb where Uid=a.Uid and Cdt<a.Cdt)Pdt from #tb a --加索引，99W记录，用时18秒 select a.Uid,a.Lid,a.Cdt,(select TOP 1 Cdt from #tb where Uid=a.Uid and Cdt<a.Cdt order by Cdt desc)Pdt from #tb a --drop index #tb.tb_Uid_Cdt

中国风 2016-10-31

打赏
举报

引用 6 楼 baidu_35289351 的回复:

比你3楼的效率呢？

#3情况，用TOP 1会高一些

中国风 2016-10-30

打赏
举报

SQL 2005~08用

--1、
;WITH CTET
AS
(SELECT *,ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY ListingID) AS RN FROM Tab
)
SELECT a.*,b.CreatedDT AS PreCreatedDT FROM CTET AS a INNER JOIN CTET AS b ON a.UserID=b.UserID AND a.RN=b.RN+1
--2、

SELECT *,(SELECT SUM(sale) FROM Tab WHERE id=a.id AND date<=a.date) FROM Tab  AS a

中国风 2016-10-30

打赏
举报

--1、
SELECT *,LAG(CreatedDT)OVER(PARTITION BY UserID ORDER BY ListingID) AS PreCreatedDT FROM Tab

--2、
SELECT *,SUM(sale)OVER(PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM Tab