数据透视表中级班练习六

JLxiangwei · 发表于 2014-5-23 19:31

本帖最后由 JLxiangwei 于 2014-9-16 15:00 编辑

SQL练习之统计时间段.rar (9.39 KB, 下载次数: 105)

lasharks · 发表于 2014-5-23 22:13

本帖最后由 lasharks 于 2014-5-24 20:20 编辑

做的很复杂，有空再优化

select min([start_date]) as [start_date],max([end_date]) as [end_date] from (select a.*,(select min(b.[start_date]) from [date$a1:c11]b where (a.[start_date]>=b.[start_date] and a.[start_date]<=b.[end_date]) or a.[start_date]-1=b.[end_date] ) as tt,(select max(c.[end_date]) from [date$a1:c11]c where (a.[end_date]>=c.[start_date] and a.[end_date]<=c.[end_date]) or a.[end_date]+1=c.[start_date]) as ttt from [date$a1:c11]a)d group by tt,ttt

复制代码

---------------------------------------------------------------------------------------------------------------------------------------------------
向老师，追加了几个数据源后，发现上面的代码有漏洞，改了一下，如下

select tp1 as [start_date],max(tp2) as [end_date] from (select min(tp1) as tp1,tp2 as tp2 from (select (select min(b.[start_date]) from [date$a:c]b where (a.[start_date]>=b.[start_date] and a.[start_date]<=b.[end_date]) or a.[start_date]-1=b.[end_date] ) as tp1,(select max(c.[end_date]) from [date$a:c]c where (a.[end_date]>=c.[start_date] and a.[end_date]<=c.[end_date]) or a.[end_date]+1=c.[start_date]) as tp2 from [date$a:c]a) group by tp2) group by tp1

复制代码

先用2个内嵌子查询，对日期可能有重叠的日期段，始末日期做调整

select (select min(b.[start_date]) from [date$a:c]b where (a.[start_date]>=b.[start_date] and a.[start_date]<=b.[end_date]) or a.[start_date]-1=b.[end_date] ) as tp1,(select max(c.[end_date]) from [date$a:c]c where (a.[end_date]>=c.[start_date] and a.[end_date]<=c.[end_date]) or a.[end_date]+1=c.[start_date]) as tp2 from [date$a:c]a

复制代码

然后外面在套2次查询，合并掉相同的始，末日期
---------------------------------------------------------------------------------------------------------------------------------------------------

箫风 · 发表于 2014-5-25 21:12

游客，如果您要查看本帖隐藏内容请回复

天空的雨 · 发表于 2014-5-26 10:52

本帖最后由天空的雨于 2014-5-27 14:55 编辑

起始值：小于1的没有则跳出来，因3月4日没出来，考虑End_Date字段中有比其小1的数。
select  DISTINCT Start_Date from[Date$]a where
(select count(Start_Date) from (select Start_Date from [Date$] union
select End_Date from[Date$]) where Start_Date-a.Start_Date=-1)=0

终值：大于1的没有则跳出来。
select DISTINCT End_Date from[Date$]c
where (select count(End_Date) from[Date$]
where End_Date-c.End_Date=1 )=0
再分别标序组后。

答：一表连接的方法
select Start_Date,End_Date from (select Start_Date,(select count(Start_Date) from(select  DISTINCT Start_Date from[Date$]a where
(select count(Start_Date) from (select Start_Date from [Date$] union
select End_Date from[Date$]) where Start_Date-a.Start_Date=-1)=0) where Start_Date>b.Start_Date) as  次 from
(select  DISTINCT Start_Date from[Date$]a where
(select count(Start_Date) from (select Start_Date from [Date$] union
select End_Date from[Date$]) where Start_Date-a.Start_Date=-1)=0)b)f,(select End_Date,(select count(End_Date)
from(select DISTINCT End_Date from[Date$]c
where (select count(End_Date) from[Date$]
where End_Date-c.End_Date=1 )=0  and
End_Date> (select min(End_Date) from[Date$])) where End_Date>d.End_Date) as 次 from
(select DISTINCT End_Date from[Date$]c
where (select count(End_Date) from[Date$]
where End_Date-c.End_Date=1 )=0  and
End_Date> (select min(End_Date) from[Date$]))d)g where f.次= g.次

二直接分析法：
SELECT DISTINCT (SELECT max(Start_Date) FROM (select  Start_Date from[Date$]a where  (select count(Start_Date) from (select Start_Date from [Date$] union  select End_Date from[Date$]) where Start_Date-a.Start_Date=-1)=0)WHERE Start_Date<=F.Start_Date) AS Start_Date,(SELECT min(End_Date) FROM (select End_Date from[Date$]c where (select count(End_Date) from[Date$] where End_Date-c.End_Date=1 )=0)WHERE End_Date>=F.End_Date) AS End_Date from [Date$]F

zx_wl · 发表于 2014-7-10 16:02

select format$(min(rq),"yyyy/m/d") as start_date,format$(max(rq),"yyyy/m/d") as end_date from(select *,rq-(select count(*) from(select start_date as rq from [date$] union select end_date as rq from [date$]) t2 where t2.rq<t1.rq) as fz from(select start_date as rq from [date$] union select end_date as rq from [date$]) t1) group by fz

复制代码

mixboxc · 发表于 2015-3-11 16:04

提示: 作者被禁止或删除内容自动屏蔽

vbyou127 · 发表于 2015-12-30 19:15

ghhg

ZZCUEEI · 发表于 2015-12-30 21:07

学习学习

xanxus123 · 发表于 2016-5-11 14:34

pang1688 · 发表于 2019-6-24 19:41

ddddddddddddddddddddddddddddddddddddddddddddd

		自动登录	找回密码
密码			注册

mixboxc mixboxc 当前离线积分 35 IP卡狗仔卡	发表于 2015-3-11 16:04 \| 显示全部楼层提示: 作者被禁止或删除内容自动屏蔽
mixboxc mixboxc 当前离线积分 35 IP卡狗仔卡
	回复使用道具举报显身卡