问一个比较复杂的查询

2010-01-08 01:53:07 225 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

mongoDB查询，高级查询，含java操作mongo查询 MongoDB是一个基于分布式文件存储的数据库。由C++语言编写。旨在为WEB应用提供可扩展的高性能数据存储解决方案。 MongoDB是一个介于关系数据库和非关系数据库之间的产品，是非关系数据库当中功能最丰富，最像关系数据库的。他支持的数据结构非常松散，是类似json的bson格式，因此可以存储比较复杂的数据类型。Mongo最大的特点是他支持的查询语言非常强大，其语法有点类似于面向对象阅读详情

昨天问的问题没弄清楚，今天再来问高手一次，希望我今天问题描述清楚了，也希望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

结果的第二列是结束时间的年份，最后一列是相同职务的人时间重叠的时间次数

...全文

一个跨库复杂查询的SQL优化的案例导读为了帮客户出一个报表，需要跨三个库进行一个复杂的查询，用到了 in，union all，left join等关键字，其中几个字段还需要进行SUM，Count等计算。未优化前查询耗时368秒。待优化场景先看一下客户程序员写的这个复杂的SQL语句吧，看了都头大。虽说辉哥见多识广，还是被这个SQL吓到了。 select distinct a.cn,e.chinese_nam 阅读详情

cattycat

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

+ 关注 + 加入社区

切换为时间正序

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

问一个比较复杂的查询

相关推荐