求一有难度的SQL语句，在线等，请指点，不清楚之处留言，可秒回

weixin_42068029 2018-06-06 02:06:14 


--建表脚本

 SELECT *   INTO A_Table FROM (

 SELECT 1 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-01' AS LTDate 

   UNION ALL

 SELECT 2 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-02' AS LTDate 

   UNION ALL

 SELECT 6 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-03' AS LTDate 

   UNION ALL

 SELECT 9 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-04' AS LTDate 

   UNION ALL

 SELECT 11 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-05' AS LTDate 

   UNION ALL

 SELECT 13 AS TID,'1005' AS PIID,'项目5' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-07' AS LTDate 

   UNION ALL

 SELECT 15 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-08' AS LTDate 

   UNION ALL

 SELECT 17 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-09' AS LTDate 

   UNION ALL

 SELECT 18 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-13' AS LTDate 

   UNION ALL

 SELECT 20 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-14' AS LTDate  

   UNION ALL

 SELECT 22 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-15' AS LTDate  

) AS A



SELECT * FROM A_Table 

--想加载出来的结果如下

SELECT * INTO A_Table_Result FROM 

(

SELECT '1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-01' AS '开始日期','2018-01-01' AS '结束日期',1 AS '天数'

UNION ALL

SELECT '1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-02' AS '开始日期','2018-01-02' AS '结束日期',1 AS '天数'

UNION ALL

SELECT '1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-03' AS '开始日期','2018-01-03' AS '结束日期',1 AS '天数'

UNION ALL

SELECT '1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-04' AS '开始日期','2018-01-05' AS '结束日期',2 AS '天数'

UNION ALL

SELECT '1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-08' AS '开始日期','2018-01-08' AS '结束日期',1 AS '天数'

UNION ALL

SELECT '1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-09' AS '开始日期','2018-01-09' AS '结束日期',1 AS '天数'

UNION ALL

SELECT '1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-13' AS '开始日期','2018-01-15' AS '结束日期',3 AS '天数'

) AS A9



SELECT * FROM A_Table_Result



-- 解释：除去 市内 AreaID 为2的 其他的数据还好计算 此处难点就在于AreaID 为2的数据，不管是否同一个项目 只要是AreaID ----为2的并且满足连续2天以上的数据则加载出来

-- 不能满足连续两天以上的则不加载，比如 TID 为1跟2的 项目不是同一个项目 但AreaID 为2，又是连续的两天，所以数据则加---载了，TID为13的数据 则虽然AreaID 为2

-- 但是只有7号一天数据 所以则不加载



--原始数据：

TID	PIID	PIName	AreaID	AreaName	LTDate

1	1001	项目1	2	市内	2018-01-01

2	1002	项目2	2	市内	2018-01-02

6	1003	项目3	3	市外	2018-01-03

9	1004	项目4	4	省外	2018-01-04

11	1004	项目4	4	省外	2018-01-05

13	1005	项目5	2	市内	2018-01-07

15	1004	项目4	4	省外	2018-01-08

17	1003	项目3	3	市外	2018-01-09

18	1003	项目3	3	市外	2018-01-13

20	1003	项目3	3	市外	2018-01-14

22	1003	项目3	3	市外	2018-01-15



--想要的结果数据：

PIID	PIName	AreaID	AreaName	开始日期	结束日期	天数

1001	项目1	2	市内	2018-01-01	2018-01-01	1

1002	项目2	2	市内	2018-01-02	2018-01-02	1

1003	项目3	3	市外	2018-01-03	2018-01-03	1

1004	项目4	4	省外	2018-01-04	2018-01-05	2

1004	项目4	4	省外	2018-01-08	2018-01-08	1

1003	项目3	3	市外	2018-01-09	2018-01-09	1

1003	项目3	3	市外	2018-01-13	2018-01-15	3





-- 解释：除去 市内 AreaID 为2的 其他的数据还好计算 此处难点就在于AreaID 为2的数据，不管是否同一个项目 只要是AreaID ----为2的并且满足连续2天以上的数据则加载出来

-- 不能满足连续两天以上的则不加载，比如 TID 为1跟2的 项目不是同一个项目 但AreaID 为2，又是连续的两天，所以数据则加---载了，TID为13的数据 则虽然AreaID 为2

-- 但是只有7号一天数据 所以则不加载
...全文
232 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
weixin_42068029 2018-06-06
  • 打赏
  • 举报
 回复
引用 11 楼 zbdzjx 的回复:
再试一下这个吧。 
with t1 as
(
 SELECT 1 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName, '2018-01-01' AS LTDate 
   UNION ALL
 SELECT 2 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-02' AS LTDate 
   UNION ALL
 SELECT 6 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-03' AS LTDate 
   UNION ALL
 SELECT 9 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-04' AS LTDate 
   UNION ALL
 SELECT 11 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-05' AS LTDate 
   UNION ALL
 SELECT 13 AS TID,'1005' AS PIID,'项目5' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-07' AS LTDate 
   UNION ALL
 SELECT 15 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-08' AS LTDate 
   UNION ALL
 SELECT 17 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-09' AS LTDate 
   UNION ALL
 SELECT 18 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-13' AS LTDate 
   UNION ALL
 SELECT 20 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-14' AS LTDate  
   UNION ALL
 SELECT 22 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-15' AS LTDate  
)
, t2 as
(
  select ROW_NUMBER() over(order by TID) id, * from t1
)
, t3 as
(
  select ROW_NUMBER() over(order by min(ltdate)) ri, PIID, PIName, AreaID, AreaName, convert(datetime, MIN(LTDate))ltdate_x, convert(datetime, MAX(LTDate)) ltdate_d, DATEDIFF(day, min(ltdate), max(ltdate))+1 ltdate_c
  from t2 
  group by PIID, PIName, AreaID, AreaName, convert(datetime, LTDate)-id
)
select * from t3 where ri not in (
  select ri
  from t3 a 
  where AreaID=2 
  and not exists (select 1 from t3 b where AreaID=2 and a.ltdate_x-1 between b.ltdate_x and b.ltdate_d)
  and not exists (select 1 from t3 b where AreaID=2 and a.ltdate_d+1 between b.ltdate_x and b.ltdate_d)
)
太感谢您好 我好好分析分析你的语句。
weixin_42068029 2018-06-06
  • 打赏
  • 举报
 回复 

-- 或许这样也可以的 将下列数据
TID	PIID	PIName	AreaID	AreaName	LTDate
1	1001	项目1	2	市内	2018-01-01
2	1002	项目2	2	市内	2018-01-02
13	1005	项目5	2	市内	2018-01-07
18	1001	项目1	2	市内	2018-01-13
20	1002	项目2	2	市内	2018-01-14
22	1003	项目3	2	市内	2018-01-15

-- 更改为 这样的数据
TID	PIID	PIName	AreaID	AreaName	LTDate
1	1001	项目1	2	市内	2018-01-01
2	1001	项目1	2	市内	2018-01-02
13	1005	项目5	2	市内	2018-01-07
18	1001	项目1	2	市内	2018-01-13
20	1001	项目1	2	市内	2018-01-14
22	1001	项目1	2	市内	2018-01-15

--就是将连续的天数的 PIID都改为连续天数开头的第一天的PIID。
-- 这个应该会会用到游标吧 我写了个游标 前两行改了 第三行改不了 请高手指点指点
-- 游标脚本如下


begin
    declare @TID int,@error int    
    declare @EID varchar(50)
    declare @PIID varchar(50)
    declare @PIName varchar(50)
    declare @sql varchar(5000)
    declare @LTDate date

    --申明游标为Uid
    declare order_cursor cursor FAST_FORWARD
    for 
    SELECT TID,EID,PIID,PIName,LTDate FROM A_Table 
    WHERE AreaID=2 
    ORDER BY eid,LTDate
    --打开游标--
    open order_cursor
    --开始循环游标变量--
    fetch next from order_cursor into @TID,@EID,@PIID,@PIName,@LTDate
    while @@FETCH_STATUS = 0    --返回被 FETCH语句执行的最后游标的状态--
        BEGIN
         PRINT STR(@TID)+' '+@EID+' '+@PIID+' '+CONVERT(VARCHAR(10),@LTDate,23)
        -- UPDATE A_Table SET PIID=@PIID,PIName=@PIName WHERE LTDate=DATEADD(DAY,1,@LTDate)
         --SET @sql='UPDATE A_Table SET PIID='+@PIID+',PIName='+@PIName+' WHERE LTDate='+CONVERT(VARCHAR(10),DATEADD(DAY,1,@LTDate),23)+''
         --PRINT @sql
            fetch next from order_cursor into @TID,@EID,@PIID,@PIName,@LTDate   --转到下一个游标
        end    
    close order_cursor  --关闭游标
    deallocate order_cursor   --释放游标
end
GO
zbdzjx 2018-06-06
  • 打赏
  • 举报
 回复
再试一下这个吧。 
with t1 as
(
 SELECT 1 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName, '2018-01-01' AS LTDate 
   UNION ALL
 SELECT 2 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-02' AS LTDate 
   UNION ALL
 SELECT 6 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-03' AS LTDate 
   UNION ALL
 SELECT 9 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-04' AS LTDate 
   UNION ALL
 SELECT 11 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-05' AS LTDate 
   UNION ALL
 SELECT 13 AS TID,'1005' AS PIID,'项目5' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-07' AS LTDate 
   UNION ALL
 SELECT 15 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-08' AS LTDate 
   UNION ALL
 SELECT 17 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-09' AS LTDate 
   UNION ALL
 SELECT 18 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-13' AS LTDate 
   UNION ALL
 SELECT 20 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-14' AS LTDate  
   UNION ALL
 SELECT 22 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-15' AS LTDate  
)
, t2 as
(
  select ROW_NUMBER() over(order by TID) id, * from t1
)
, t3 as
(
  select ROW_NUMBER() over(order by min(ltdate)) ri, PIID, PIName, AreaID, AreaName, convert(datetime, MIN(LTDate))ltdate_x, convert(datetime, MAX(LTDate)) ltdate_d, DATEDIFF(day, min(ltdate), max(ltdate))+1 ltdate_c
  from t2 
  group by PIID, PIName, AreaID, AreaName, convert(datetime, LTDate)-id
)
select * from t3 where ri not in (
  select ri
  from t3 a 
  where AreaID=2 
  and not exists (select 1 from t3 b where AreaID=2 and a.ltdate_x-1 between b.ltdate_x and b.ltdate_d)
  and not exists (select 1 from t3 b where AreaID=2 and a.ltdate_d+1 between b.ltdate_x and b.ltdate_d)
)
weixin_42068029 2018-06-06
  • 打赏
  • 举报
 回复
引用 8 楼 zbdzjx 的回复:
[quote=引用 7 楼 weixin_42068029 的回复:] [quote=引用 5 楼 zbdzjx 的回复:] 结果中，PIID为1005的那一条为什么会没有？
因为PIID为1005的那一条只有一天 所以 被剔除了 areaid=2 要连续两天和两天以上的才可以显示[/quote] 那我前面写的就不对了，我再改改。[/quote] 非常感谢
weixin_42068029 2018-06-06
  • 打赏
  • 举报
 回复
引用 4 楼 Magichi 的回复:
select *,(select count(1) from temp as t2 where t2.date=t1.date+1 ) as countday1 from temp as t1
可以根据count的结果来判断，该语句中，t2.date=t1.date+1是判断有没有等于当前日期+1的数据，如果count>0，说明有连续的两天数据
不好意思 大神 没看明白什么意思
zbdzjx 2018-06-06
  • 打赏
  • 举报
 回复
引用 7 楼 weixin_42068029 的回复:
[quote=引用 5 楼 zbdzjx 的回复:] 结果中，PIID为1005的那一条为什么会没有？
因为PIID为1005的那一条只有一天 所以 被剔除了 areaid=2 要连续两天和两天以上的才可以显示[/quote] 那我前面写的就不对了，我再改改。
weixin_42068029 2018-06-06
  • 打赏
  • 举报
 回复
引用 5 楼 zbdzjx 的回复:
结果中，PIID为1005的那一条为什么会没有？
因为PIID为1005的那一条只有一天 所以 被剔除了 areaid=2 要连续两天和两天以上的才可以显示
zbdzjx 2018-06-06
  • 打赏
  • 举报
 回复
结果中，PIID为1005的那一条为什么会没有？
Magichi 2018-06-06
  • 打赏
  • 举报
 回复 
select *,(select count(1) from temp as t2 where t2.date=t1.date+1 ) as countday1 from temp as t1
可以根据count的结果来判断，该语句中，t2.date=t1.date+1是判断有没有等于当前日期+1的数据，如果count>0，说明有连续的两天数据
zbdzjx 2018-06-06
  • 打赏
  • 举报
 回复 
with t1 as
(
 SELECT 1 AS TID,'1001' AS PIID,'项目1' AS PIName,'2' AS AreaID,'市内' AS AreaName, '2018-01-01' AS LTDate 
   UNION ALL
 SELECT 2 AS TID,'1002' AS PIID,'项目2' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-02' AS LTDate 
   UNION ALL
 SELECT 6 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-03' AS LTDate 
   UNION ALL
 SELECT 9 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-04' AS LTDate 
   UNION ALL
 SELECT 11 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-05' AS LTDate 
   UNION ALL
 SELECT 13 AS TID,'1005' AS PIID,'项目5' AS PIName,'2' AS AreaID,'市内' AS AreaName,'2018-01-07' AS LTDate 
   UNION ALL
 SELECT 15 AS TID,'1004' AS PIID,'项目4' AS PIName,'4' AS AreaID,'省外' AS AreaName,'2018-01-08' AS LTDate 
   UNION ALL
 SELECT 17 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-09' AS LTDate 
   UNION ALL
 SELECT 18 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-13' AS LTDate 
   UNION ALL
 SELECT 20 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-14' AS LTDate  
   UNION ALL
 SELECT 22 AS TID,'1003' AS PIID,'项目3' AS PIName,'3' AS AreaID,'市外' AS AreaName,'2018-01-15' AS LTDate  
)
, t2 as
(
select ROW_NUMBER() over(order by TID) id, * from t1
)
select PIID, PIName, AreaID, AreaName, MIN(LTDate), MAX(LTDate), DATEDIFF(day, min(ltdate), max(ltdate))+1
from t2 
group by PIID, PIName, AreaID, AreaName, convert(datetime, LTDate)-id
order by MIN(LTDate)
weixin_42068029 2018-06-06
  • 打赏
  • 举报
 回复
请大神指点指点
weixin_42068029 2018-06-06
  • 打赏
  • 举报
 回复
引用 1 楼 chengangcsdn 的回复:
你举例的结果有有areaid=2的啊。和你要除去的要求不符呢
不是呀 ，就是areaid=2 只有一天的就过滤 有连续两天的或者两天以上的则不过滤 所以 TID 1，2 留了下来 ，因为连续了两天 13则被剔除了 因为只有一天。
chengangcsdn 2018-06-06
  • 打赏
  • 举报
 回复
你举例的结果有有areaid=2的啊。和你要除去的要求不符呢
关于SQL的几道小题详解
关于SQL的几道小题详解 当我们拿到题目的时候，并不是急于作答，那样会得不偿失的，而是分析思路，采用什么方法，达到什么目的，还要思考有没有简单的方法或者通用的方法等等，这样才会达到以一当十的效果，这样的惯性思维其实早在我们度高中的时候就被领教了，所谓“万变不离其宗”吧。以下各题来自日常所见，或QQ群，或面试题，或博客园。 题目一：如下表所示，现需要按照收款员统计收款和退款...
【每日SQL打卡】​​​​​​​​​​​​​​​DAY 8丨判断三角形【难度简单】
活动介绍： 「数据仓库技术交流群」已经正式启动每日SQL打卡，帮助大家扎实基础，努力工作之余，别忘了自我提升。 欢迎报名和邀请小伙伴参与，一个人可能走得很快，但一群人会走得很远。 ????题目汇总(收藏复习)：https://blog.csdn.net/weixin_39032019/category_11459536.html ????发帖打卡(多种解法)：https://bbs.csdn.net/forums/ITID?typeId=18647 ????加入打卡：https://app.yi
【每日SQL打卡】​​​​​​​​​​​​​​​DAY 20丨查询球队积分【难度中等】​
每日两题，一难一易，每天进步一点点，可能会直接导致一场面试的成功
【每日SQL打卡】​​​​​​​​​​​​​​​DAY 6丨统计各专业学生人数【难度中等】
活动介绍： 「数据仓库技术交流群」已经正式启动每日SQL打卡，帮助大家扎实基础，努力工作之余，别忘了自我提升。 欢迎报名和邀请小伙伴参与，一个人可能走得很快，但一群人会走得很远。 ????题目汇总(收藏复习)：https://blog.csdn.net/weixin_39032019/category_11459536.html ????发帖打卡(多种解法)：https://bbs.csdn.net/forums/ITID?typeId=18647 ????交个朋友：https://app.yi
【SQL基础学习-03】
SQL基础学习-03
MS-SQL Server

34,588

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章