问一个比较复杂的查询

cattycat 2010-01-08 01:53:07
昨天问的问题没弄清楚，今天再来问高手一次，希望我今天问题描述清楚了，也希望SQL高手给点意见。
谢谢！

数据如下：
tduty tem_num tstartd tendd
951D 500236 2005-01-12 00:00:00.000 2005-05-01 00:00:00.000
951D 600751 2005-01-16 00:00:00.000 2005-05-20 00:00:00.000
951D 000329 2005-01-28 00:00:00.000 2005-06-03 00:00:00.000
951D 000329 2005-06-03 00:00:00.000 2005-10-13 00:00:00.000
951D 600189 2005-08-21 00:00:00.000 2005-11-28 00:00:00.000

951C 003640 2004-01-01 00:00:00.000 2005-01-25 00:00:00.000
951C 000213 2004-01-10 00:00:00.000 2005-01-28 00:00:00.000
951C 005567 2004-01-29 00:00:00.000 2005-01-16 00:00:00.000
951C 600055 2004-05-21 00:00:00.000 2005-04-14 00:00:00.000
951C 000692 2004-05-31 00:00:00.000 2005-04-01 00:00:00.000

941D 005671 2004-03-19 00:00:00.000 2005-01-12 00:00:00.000
941D 004873 2004-04-01 00:00:00.000 2005-01-05 00:00:00.000

最后返回的结果是
tduty tendyear cnt
951D 2005 3
951D 2005 2
951C 2005 5
941D 2005 2

结果的第二列是结束时间的年份，最后一列是相同职务的人时间重叠的时间次数
...全文
158 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
wang913com 2010-01-15
  • 打赏
  • 举报
 回复
想了一下，我给你一个最佳实现:

--原始数据:
CREATE VIEW test_view
AS

SELECT * FROM

(
SELECT '951D' AS tduty, '500236' AS tem_num, '2005-01-12 00:00:00.000' AS tstartd, '2005-05-01 00:00:00.000' AS tendd UNION ALL
SELECT '951D' AS tduty, '600751' AS tem_num, '2005-01-16 00:00:00.000' AS tstartd, '2005-05-20 00:00:00.000' AS tendd UNION ALL
SELECT '951D' AS tduty, '000329' AS tem_num, '2005-01-28 00:00:00.000' AS tstartd, '2005-06-03 00:00:00.000' AS tendd UNION ALL
SELECT '951D' AS tduty, '000329' AS tem_num, '2005-06-03 00:00:00.000' AS tstartd, '2005-10-13 00:00:00.000' AS tendd UNION ALL
SELECT '951D' AS tduty, '600189' AS tem_num, '2005-08-21 00:00:00.000' AS tstartd, '2005-11-28 00:00:00.000' AS tendd UNION ALL

SELECT '951C' AS tduty, '003640' AS tem_num, '2004-01-01 00:00:00.000' AS tstartd, '2005-01-25 00:00:00.000' AS tendd UNION ALL
SELECT '951C' AS tduty, '000213' AS tem_num, '2004-01-10 00:00:00.000' AS tstartd, '2005-01-28 00:00:00.000' AS tendd UNION ALL
SELECT '951C' AS tduty, '005567' AS tem_num, '2004-01-29 00:00:00.000' AS tstartd, '2005-01-16 00:00:00.000' AS tendd UNION ALL
SELECT '951C' AS tduty, '600055' AS tem_num, '2004-05-21 00:00:00.000' AS tstartd, '2005-04-14 00:00:00.000' AS tendd UNION ALL
SELECT '951C' AS tduty, '000692' AS tem_num, '2004-05-31 00:00:00.000' AS tstartd, '2005-04-01 00:00:00.000' AS tendd UNION ALL

SELECT '941D' AS tduty, '005671' AS tem_num, '2004-03-19 00:00:00.000' AS tstartd, '2005-01-12 00:00:00.000' AS tendd UNION ALL
SELECT '941D' AS tduty, '004873' AS tem_num, '2004-04-01 00:00:00.000' AS tstartd, '2005-01-05 00:00:00.000' AS tendd
)tmp



GO

--你想得到的数据的sql:

SELECT DISTINCT
tduty,
YEAR(tendd) AS tendyear,
(SELECT TOP 1 COUNT(1) + 1 FROM test_view
WHERE tduty = a.tduty AND
(
(DATEDIFF(DD,tstartd,a.tstartd)> 0 AND DATEDIFF(DD,a.tstartd,tendd)>0) OR
(DATEDIFF(DD,tstartd,a.tendd)>0 AND DATEDIFF(DD,a.tendd,tendd) >0) OR
(DATEDIFF(DD,a.tstartd,tstartd)>0 AND DATEDIFF(DD,tendd,a.tendd) >0)
)
) AS cnt

FROM test_view a
ORDER BY tduty DESC,cnt DESC


cattycat 2010-01-08
  • 打赏
  • 举报
 回复
对，我那个数据有问题。
我原来也想用游标，不过语法太复杂，没写出来。
非常感谢wugui.
dawugui 2010-01-08
  • 打赏
  • 举报
 回复
--你得把这条数据改改.这样才能到达你的需求
select '951D','000329','2005-06-03 00:00:00.000','2005-10-13 00:00:00.000' union all
-->
select '951D','000329','2005-06-04 00:00:00.000','2005-10-13 00:00:00.000' union all


create table [tb]([tduty] varchar(4),[tem_num] varchar(6),[tstartd] datetime,[tendd] datetime)

insert [tb]

select '951D','500236','2005-01-12 00:00:00.000','2005-05-01 00:00:00.000' union all

select '951D','600751','2005-01-16 00:00:00.000','2005-05-20 00:00:00.000' union all

select '951D','000329','2005-01-28 00:00:00.000','2005-06-03 00:00:00.000' union all

select '951D','000329','2005-06-04 00:00:00.000','2005-10-13 00:00:00.000' union all

select '951D','600189','2005-08-21 00:00:00.000','2005-11-28 00:00:00.000' union all

select '951C','003640','2004-01-01 00:00:00.000','2005-01-25 00:00:00.000' union all

select '951C','000213','2004-01-10 00:00:00.000','2005-01-28 00:00:00.000' union all

select '951C','005567','2004-01-29 00:00:00.000','2005-01-16 00:00:00.000' union all

select '951C','600055','2004-05-21 00:00:00.000','2005-04-14 00:00:00.000' union all

select '951C','000692','2004-05-31 00:00:00.000','2005-04-01 00:00:00.000' union all

select '941D','005671','2004-03-19 00:00:00.000','2005-01-12 00:00:00.000' union all

select '941D','004873','2004-04-01 00:00:00.000','2005-01-05 00:00:00.000'

create table t2(id int, tduty  varchar(10), [year] int,   cnt  int)

go



declare @tduty1 varchar(10),@tduty2 varchar(10),@tstartd1 datetime,@tstartd2 datetime ,@tendd1 datetime , @tendd2 datetime;

declare @id as int

set @id = 0

set @tduty1 = ''

set @tduty2 = ''

set @tstartd1 = getdate()

set @tstartd2 = getdate()

set @tendd1 = getdate()

set @tendd2 = getdate()



declare cur cursor fast_forward for

  select tduty,tstartd,tendd from tb order by tduty,tstartd,tendd;

open cur;

fetch next from cur into @tduty1,@tstartd1,@tendd1;

while @@fetch_status=0

begin

   if @tduty1 = @tduty2

      begin

        if @tstartd1 between @tstartd2 and @tendd2 or @tendd1 between @tstartd2 and @tendd2 or

           @tstartd2 between @tstartd1 and @tendd1 or @tendd2 between @tstartd1 and @tendd1

           update t2 set [year] = year(@tendd1) , cnt = cnt + 1 where id = @id

        else

           begin

           set @id = @id + 1

           insert t2 select @id , @tduty1 , year(@tendd1) , 1

           end

      end 

   else

      begin

      set @id = @id + 1

      insert t2 select @id , @tduty1 , year(@tendd1) , 1

      end

   set @tduty2 = @tduty1

   set @tstartd2 = @tstartd1

   set @tendd2 = @tendd1

   fetch next from cur into @tduty1,@tstartd1,@tendd1;

end

close cur;

deallocate cur;



select * from t2 order by id



drop table tb , t2



/*

id          tduty      year        cnt         

----------- ---------- ----------- ----------- 

1           941D       2005        2

2           951C       2005        5

3           951D       2005        3

4           951D       2005        2



（所影响的行数为 4 行）





*/
dawugui 2010-01-08
  • 打赏
  • 举报
 回复
--按照你原来的数据
create table [tb]([tduty] varchar(4),[tem_num] varchar(6),[tstartd] datetime,[tendd] datetime)

insert [tb]

select '951D','500236','2005-01-12 00:00:00.000','2005-05-01 00:00:00.000' union all

select '951D','600751','2005-01-16 00:00:00.000','2005-05-20 00:00:00.000' union all

select '951D','000329','2005-01-28 00:00:00.000','2005-06-03 00:00:00.000' union all --这里和下面一条出现交叉.

select '951D','000329','2005-06-03 00:00:00.000','2005-10-13 00:00:00.000' union all

select '951D','600189','2005-08-21 00:00:00.000','2005-11-28 00:00:00.000' union all

select '951C','003640','2004-01-01 00:00:00.000','2005-01-25 00:00:00.000' union all

select '951C','000213','2004-01-10 00:00:00.000','2005-01-28 00:00:00.000' union all

select '951C','005567','2004-01-29 00:00:00.000','2005-01-16 00:00:00.000' union all

select '951C','600055','2004-05-21 00:00:00.000','2005-04-14 00:00:00.000' union all

select '951C','000692','2004-05-31 00:00:00.000','2005-04-01 00:00:00.000' union all

select '941D','005671','2004-03-19 00:00:00.000','2005-01-12 00:00:00.000' union all

select '941D','004873','2004-04-01 00:00:00.000','2005-01-05 00:00:00.000'

create table t2(tduty  varchar(10), [year] int,   cnt  int)

go



declare @tduty1 varchar(10),@tduty2 varchar(10),@tstartd1 datetime,@tstartd2 datetime ,@tendd1 datetime , @tendd2 datetime;

set @tduty1 = ''

set @tduty2 = ''

set @tstartd1 = getdate()

set @tstartd2 = getdate()

set @tendd1 = getdate()

set @tendd2 = getdate()



declare cur cursor fast_forward for

  select tduty,tstartd,tendd from tb order by tduty,tstartd,tendd;

open cur;

fetch next from cur into @tduty1,@tstartd1,@tendd1;

while @@fetch_status=0

begin

   if @tduty1 = @tduty2

      begin

        if @tstartd1 between @tstartd2 and @tendd2 or @tendd1 between @tstartd2 and @tendd2 or

           @tstartd2 between @tstartd1 and @tendd1 or @tendd2 between @tstartd1 and @tendd1

           update t2 set [year] = year(@tendd1) , cnt = cnt + 1 where tduty = @tduty1 and [year] = year(@tendd2)

        else

           begin

           insert t2 select @tduty1 , year(@tendd1) , 1

           end

      end 

   else

      begin

      insert t2 select @tduty1 , year(@tendd1) , 1

      end

   set @tduty2 = @tduty1

   set @tstartd2 = @tstartd1

   set @tendd2 = @tendd1

   fetch next from cur into @tduty1,@tstartd1,@tendd1;

end

close cur;

deallocate cur;



select * from t2 order by tduty , [year]



drop table tb , t2



/*

tduty      year        cnt         

---------- ----------- ----------- 

941D       2005        2

951C       2005        5

951D       2005        5



（所影响的行数为 3 行）

*/
dawugui 2010-01-08
  • 打赏
  • 举报
 回复
--使用油标完成,不过你的结果貌似有点问题.

create table [tb]([tduty] varchar(4),[tem_num] varchar(6),[tstartd] datetime,[tendd] datetime)

insert [tb]

select '951D','500236','2005-01-12 00:00:00.000','2005-05-01 00:00:00.000' union all

select '951D','600751','2005-01-16 00:00:00.000','2005-05-20 00:00:00.000' union all

select '951D','000329','2005-01-28 00:00:00.000','2005-06-03 00:00:00.000' union all

select '951D','000329','2005-06-04 00:00:00.000','2005-10-13 00:00:00.000' union all

select '951D','600189','2005-08-21 00:00:00.000','2005-11-28 00:00:00.000' union all

select '951C','003640','2004-01-01 00:00:00.000','2005-01-25 00:00:00.000' union all

select '951C','000213','2004-01-10 00:00:00.000','2005-01-28 00:00:00.000' union all

select '951C','005567','2004-01-29 00:00:00.000','2005-01-16 00:00:00.000' union all

select '951C','600055','2004-05-21 00:00:00.000','2005-04-14 00:00:00.000' union all

select '951C','000692','2004-05-31 00:00:00.000','2005-04-01 00:00:00.000' union all

select '941D','005671','2004-03-19 00:00:00.000','2005-01-12 00:00:00.000' union all

select '941D','004873','2004-04-01 00:00:00.000','2005-01-05 00:00:00.000'

create table t2(tduty  varchar(10), [year] int,   cnt  int)

go



declare @tduty1 varchar(10),@tduty2 varchar(10),@tstartd1 datetime,@tstartd2 datetime ,@tendd1 datetime , @tendd2 datetime;

set @tduty1 = ''

set @tduty2 = ''

set @tstartd1 = getdate()

set @tstartd2 = getdate()

set @tendd1 = getdate()

set @tendd2 = getdate()



declare cur cursor fast_forward for

  select tduty,tstartd,tendd from tb order by tduty,tstartd,tendd;

open cur;

fetch next from cur into @tduty1,@tstartd1,@tendd1;

while @@fetch_status=0

begin

   if @tduty1 = @tduty2

      begin

        if @tstartd1 between @tstartd2 and @tendd2 or @tendd1 between @tstartd2 and @tendd2 or

           @tstartd2 between @tstartd1 and @tendd1 or @tendd2 between @tstartd1 and @tendd1

           update t2 set [year] = year(@tendd1) , cnt = cnt + 1 where tduty = @tduty1 and [year] = year(@tendd2)

        else

           begin

           insert t2 select @tduty1 , year(@tendd1) , 1

           end

      end 

   else

      begin

      insert t2 select @tduty1 , year(@tendd1) , 1

      end

   set @tduty2 = @tduty1

   set @tstartd2 = @tstartd1

   set @tendd2 = @tendd1

   fetch next from cur into @tduty1,@tstartd1,@tendd1;

end

close cur;

deallocate cur;



select * from t2 order by tduty , [year]



drop table tb , t2



/*

tduty      year        cnt         

---------- ----------- ----------- 

941D       2005        2

951C       2005        5

951D       2005        4

951D       2005        2



（所影响的行数为 4 行）

*/
cattycat 2010-01-08
  • 打赏
  • 举报
 回复
最后计算时间有交集的记录个数，按tduty来分组。
cattycat 2010-01-08
  • 打赏
  • 举报
 回复
说错了，不是重叠，是时间区间是相交的，也就是有共同的交集。很奇怪怎么有这种需求，不过人家让这么算，没办法。
百年树人 2010-01-08
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 cattycat 的回复:]
不好意思，我没说清楚，与tem_num无关，按tduty分组，次数正好与时间重叠个数有关，我举得例子前3个正好重叠，所以个数是3，接下来2个是1组，剩下的tduty是951C, 941D的时间都是重叠的，所以个数是5，2。
[/Quote]
tduty tem_num tstartd tendd
951D 500236 2005-01-12 00:00:00.000 2005-05-01 00:00:00.000
951D 600751 2005-01-16 00:00:00.000 2005-05-20 00:00:00.000
951D 000329 2005-01-28 00:00:00.000 2005-06-03 00:00:00.000

前3个为什么是重叠的？
dawugui 2010-01-08
  • 打赏
  • 举报
 回复
我怎么算出这么个结果?

create table [tb]([tduty] varchar(4),[tem_num] varchar(6),[tstartd] datetime,[tendd] datetime)

insert [tb]

select '951D','500236','2005-01-12 00:00:00.000','2005-05-01 00:00:00.000' union all

select '951D','600751','2005-01-16 00:00:00.000','2005-05-20 00:00:00.000' union all

select '951D','000329','2005-01-28 00:00:00.000','2005-06-03 00:00:00.000' union all

select '951D','000329','2005-06-03 00:00:00.000','2005-10-13 00:00:00.000' union all

select '951D','600189','2005-08-21 00:00:00.000','2005-11-28 00:00:00.000' union all

select '951C','003640','2004-01-01 00:00:00.000','2005-01-25 00:00:00.000' union all

select '951C','000213','2004-01-10 00:00:00.000','2005-01-28 00:00:00.000' union all

select '951C','005567','2004-01-29 00:00:00.000','2005-01-16 00:00:00.000' union all

select '951C','600055','2004-05-21 00:00:00.000','2005-04-14 00:00:00.000' union all

select '951C','000692','2004-05-31 00:00:00.000','2005-04-01 00:00:00.000' union all

select '941D','005671','2004-03-19 00:00:00.000','2005-01-12 00:00:00.000' union all

select '941D','004873','2004-04-01 00:00:00.000','2005-01-05 00:00:00.000'

go



--需要使用一个临时表来拆分时间。

SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b 



select tduty , max([year]) [year], count(1) cnt from

(

  select distinct tduty,tem_num,year(tstartd + n.id) [year] from tb dt , # n where tstartd + n.id <= tendd

) t

group by tduty 

order by tduty 



drop table tb , # 



/*

tduty year        cnt 

----- ----------- ----------- 

941D  2005        4

951C  2005        10

951D  2005        4



（所影响的行数为 3 行）

*/
nianran520 2010-01-08
  • 打赏
  • 举报
 回复
时间重叠是怎么算的
cattycat 2010-01-08
  • 打赏
  • 举报
 回复
不好意思，我没说清楚，与tem_num无关，按tduty分组，次数正好与时间重叠个数有关，我举得例子前3个正好重叠，所以个数是3，接下来2个是1组，剩下的tduty是951C, 941D的时间都是重叠的，所以个数是5，2。
百年树人 2010-01-08
  • 打赏
  • 举报
 回复
只有951D那里有个'000329'是重复的，所以就把它挑出来了（如果数据还有重复的同样也可以挑出来），其他的就直接统计次数
cattycat 2010-01-08
  • 打赏
  • 举报
 回复
结果倒是对的，我不知道对其他的数据是否依然可以正确计算。
重叠时间区间可以按你写的那个来统计么？
百年树人 2010-01-08
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 cattycat 的回复:]
按开始和结束时间统计区间重叠的个数，2005是取year(tendd)年份。
[/Quote]

是4楼那样吗？
cattycat 2010-01-08
  • 打赏
  • 举报
 回复
按开始和结束时间统计区间重叠的个数，2005是取year(tendd)年份。
百年树人 2010-01-08
  • 打赏
  • 举报
 回复
猜一个
---测试数据---

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([tduty] varchar(4),[tem_num] varchar(6),[tstartd] datetime,[tendd] datetime)

insert [tb]

select '951D','500236','2005-01-12 00:00:00.000','2005-05-01 00:00:00.000' union all

select '951D','600751','2005-01-16 00:00:00.000','2005-05-20 00:00:00.000' union all

select '951D','000329','2005-01-28 00:00:00.000','2005-06-03 00:00:00.000' union all

select '951D','000329','2005-06-03 00:00:00.000','2005-10-13 00:00:00.000' union all

select '951D','600189','2005-08-21 00:00:00.000','2005-11-28 00:00:00.000' union all

select '951C','003640','2004-01-01 00:00:00.000','2005-01-25 00:00:00.000' union all

select '951C','000213','2004-01-10 00:00:00.000','2005-01-28 00:00:00.000' union all

select '951C','005567','2004-01-29 00:00:00.000','2005-01-16 00:00:00.000' union all

select '951C','600055','2004-05-21 00:00:00.000','2005-04-14 00:00:00.000' union all

select '951C','000692','2004-05-31 00:00:00.000','2005-04-01 00:00:00.000' union all

select '941D','005671','2004-03-19 00:00:00.000','2005-01-12 00:00:00.000' union all

select '941D','004873','2004-04-01 00:00:00.000','2005-01-05 00:00:00.000'

 

---查询---

select tduty,tendd,sum(cnt) as cnt

from(

select tduty,tem_num,year(tendd) as tendd,count(1) as cnt

from tb

group by tduty,tem_num,year(tendd)

) t

group by tduty,tendd,cnt

order by tduty desc,cnt desc



---结果---

tduty tendd       cnt         

----- ----------- ----------- 

951D  2005        3

951D  2005        2

951C  2005        5

941D  2005        2



（所影响的行数为 4 行）

百年树人 2010-01-08
  • 打赏
  • 举报
 回复
tduty tendyear cnt
951D 2005 3
951D 2005 2

这个是怎么来的？
nianran520 2010-01-08
  • 打赏
  • 举报
 回复
没看明白
cattycat 2010-01-08
  • 打赏
  • 举报
 回复
不好意思，数据格式有点乱，再贴一下。

tduty tem_num tstartd tendd
951D 500236 2005-01-12 00:00:00.000 2005-05-01 00:00:00.000
951D 600751 2005-01-16 00:00:00.000 2005-05-20 00:00:00.000
951D 000329 2005-01-28 00:00:00.000 2005-06-03 00:00:00.000
951D 000329 2005-06-03 00:00:00.000 2005-10-13 00:00:00.000
951D 600189 2005-08-21 00:00:00.000 2005-11-28 00:00:00.000

951C 003640 2004-01-01 00:00:00.000 2005-01-25 00:00:00.000
951C 000213 2004-01-10 00:00:00.000 2005-01-28 00:00:00.000
951C 005567 2004-01-29 00:00:00.000 2005-01-16 00:00:00.000
951C 600055 2004-05-21 00:00:00.000 2005-04-14 00:00:00.000
951C 000692 2004-05-31 00:00:00.000 2005-04-01 00:00:00.000

941D 005671 2004-03-19 00:00:00.000 2005-01-12 00:00:00.000
941D 004873 2004-04-01 00:00:00.000 2005-01-05 00:00:00.000
mybatis的复杂sql查询
在利用mybatis做查询的时候，一般返回结果用resulttype，这种情况必须是查询的结果在对应 的pojo类中有对应的，一般都是单表查询，但是对于一些复杂的情况，比如需要用到多表查询的时候，resultType不再适用，此时...
JPA中@query注解的复杂查询
那么如果说我们要进行一个复合查询，两张表关联，然后要求其中的字符如果为空，那么就不走这个查询怎么办？这一条sql语句表示，如果说咱们的name要进行模糊查询，但是前端又可以传一个空值进来（事真多啊！不过到了...
解决mongo大数据量下复杂查询的性能问题
解决mongo大数据量下复杂查询的性能问题 本篇背景是我们获取应用运行时产出的日志，做展示。比如机器人运行时日志，用户问句对应意图这些信息，对于业务方是关注的， 他可以检索相关日志信息，对不正确问句意图进行...
mysql集合基本查询和复杂查询大全（并 交差，笛卡尔积）
一、数据库基本集合查询  首先我们创建两个表  create table R(A char(2),B char(2),C char(2));  create table S(A char(2),B char(2),C char(2));  然后导入数据  insert into R values ('a1','b3','c2'...
Mybatis注解形式tkmapper中Example的复杂查询
以一个基础查询为例子 SELECT * FROM equip_info 很多时候会遇到这样 的情况，例如： SELECT * FROM rmp_equip_info WHERE is_active = 'Y' AND ( create_by = '560129864519258146' OR arrive_duty IN ...
疑难问题

22,210

社区成员

121,726

社区内容

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

加载中

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

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

+ 用AI写文章