跪求跨天查询的合理解决方案(150分)

kukow · 2008-03-15

我们经常用到日期查询,要写个复杂的查询有时很是复杂,现在遇到一个跨天查询的例子!如下: use tempdb go create table T1(id int,name varchar(4),kind bit,start_time varchar(5),end_time varchar(5)) insert T1 select 1,'早',0,'07:01','10:00' union all select 2,'中',0,'10:01','15:00' union all select 3,'晚',0,'15:01','22:00' union all select 4,'夜',1,'22:01','07:00'   (kind 为1表示跨天) create table T2(id int identity(1,1),dt datetime,amount money) insert t2 select '2007-05-02 10:34:10',7.5 union all select '2007-05-03 03:10:10',11.1 union all select '2007-05-03 15:12:11',5.5 union all select '2007-05-04 19:10:10',3.5 我要查询出以下结果: 日期    次数(早)     金额(早)   次数(中)     金额(中) 次数(晚)     金额(晚) 次数(夜)     金额(夜)   合计次数  合计金额 如何写SQL语句 分不够可以再加...

kukow · 2008-03-15

use tempdb go create table T1(id int,name varchar(4),kind bit,start_time varchar(5),end_time varchar(5)) insert T1 select 1,'早',0,'07:01','10:00' union all select 2,'中',0,'10:01','15:00' union all select 3,'晚',0,'15:01','22:00' union all select 4,'夜',1,'22:01','07:00'   (kind 为1表示跨天) create table T2(id int identity(1,1),dt datetime,amount money) insert t2 select '2007-05-02 10:34:10',7.5 union all select '2007-05-03 03:10:10',11.1 union all select '2007-05-03 15:12:11',5.5 union all select '2007-05-04 19:10:10',3.5 我要查询出以下结果: 日期    次数(早)     金额(早)   次数(中)     金额(中) 次数(晚)     金额(晚) 次数(夜)     金额(夜)   合计次数  合计金额 2007-05-02  0         0        1          7.5      0             0      1           11.1        2       18.6 2007-05-03  0         0        0          0        1             5.5    0           0           1       5.5 2007-05-04  0         0        0          0        1             3.5    0           0           1       3.5 我要得到以上结果,如何写查询语句....

sunnyfairy · 2008-03-15

create table T1(id int,name varchar(4),kind bit,start_time varchar(5),end_time varchar(5)) insert T1 select 1,'早',0,'07:01','10:00' union all select 2,'中',0,'10:01','15:00' union all select 3,'晚',0,'15:01','22:00' union all select 4,'夜',1,'22:01','07:00' create table T2(id int identity(1,1),dt datetime,amount money) insert t2 select '2007-05-02 10:34:10',7.5 union all select '2007-05-02 10:50:10',100.00 union all select '2007-05-03 03:10:10',11.1 union all select '2007-05-03 10:50:10',100.00 union all select '2007-05-03 23:50:10',100.00 union all select '2007-05-03 15:12:11',5.5 union all select '2007-05-04 19:10:10',3.5 union all select '2007-05-05 07:10:10',10 union all select '2007-05-05 08:10:10',12 union all select '2007-05-05 09:10:10',13.6 union all select '2007-05-04 04:50:10',100.00 select * from T1 select * from T2 select  identity(int, 1, 1) id, convert(varchar(10), dt, 120) date, count(*) ct, sum(amount) je into #t1 from t2 where dt >=  convert(datetime, (convert(varchar(10), dt, 120) + ' 07:00:00')) and dt < convert(datetime, (convert(varchar(10), dt, 120) + ' 10:00:00')) group by convert(varchar(10), dt, 120) select  identity(int, 1, 1) id, convert(varchar(10), dt, 120) date, count(*) ct, sum(amount) je into #t2 from t2 where dt >=  convert(datetime, (convert(varchar(10), dt, 120) + ' 10:00:00')) and dt < convert(datetime, (convert(varchar(10), dt, 120) + ' 15:00:00')) group by convert(varchar(10), dt, 120) select  identity(int, 1, 1) id, convert(varchar(10), dt, 120) date, count(*) ct, sum(amount) je into #t3 from t2 where dt >=  convert(datetime, (convert(varchar(10), dt, 120) + ' 15:00:00')) and dt < convert(datetime, (convert(varchar(10), dt, 120) + ' 22:00:00')) group by convert(varchar(10), dt, 120) select identity(int, 1, 1) id, date, sum(ct) ct, sum(je) je into #t4 from (   select  convert(varchar(10), dt, 120) date, count(*) ct, sum(amount) je   from t2   where dt >=  convert(datetime, (convert(varchar(10), dt, 120) + ' 22:00:00')) and         dt < convert(datetime, (convert(varchar(10), dt, 120) + ' 23:59:59'))   group by convert(varchar(10), dt, 120) union all   select convert(varchar(10), convert(datetime, date) - 1, 120) date, ct, je from   (select  convert(varchar(10), dt, 120) date, count(*) ct, sum(amount) je    from t2    where dt >=  convert(datetime, (convert(varchar(10), dt, 120) + ' 00:00:00')) and         dt < convert(datetime, (convert(varchar(10), dt, 120) + ' 07:00:00'))   group by convert(varchar(10), dt, 120)) a) b group by date Create table Tmp(date varchar(10), zc int, zj numeric(18, 9),                                    hc int, hj numeric(18, 9),                                    wc int, wj numeric(18, 9),                                    yc int, yj numeric(18, 9)) insert into tmp(date) select '2007-05-02' union all select '2007-05-03' union all select '2007-05-04' union all select '2007-05-05' select * from tmp update tmp set zc = ct, zj = je from #t1 where #t1.date = tmp.date update tmp set hc = ct, hj = je from #t2 where #t2.date = tmp.date update tmp set wc = ct, wj = je from #t3 where #t3.date = tmp.date update tmp set yc = ct, yj = je from #t4 where #t4.date = tmp.date select * from tmp

sunnyfairy · 2008-03-15

1 早 0 07:01 10:00 2 中 0 10:01 15:00 3 晚 0 15:01 22:00 4 夜 1 22:01 07:00 1 2007-05-02 10:34:10.000 7.5000 2 2007-05-02 10:50:10.000 100.0000 3 2007-05-03 03:10:10.000 11.1000 4 2007-05-03 10:50:10.000 100.0000 5 2007-05-03 23:50:10.000 100.0000 6 2007-05-03 15:12:11.000 5.5000 7 2007-05-04 19:10:10.000 3.5000 8 2007-05-05 07:10:10.000 10.0000 9 2007-05-05 08:10:10.000 12.0000 10 2007-05-05 09:10:10.000 13.6000 11 2007-05-04 04:50:10.000 100.0000 2007-05-02 NULL NULL NULL NULL NULL NULL NULL NULL 2007-05-03 NULL NULL NULL NULL NULL NULL NULL NULL 2007-05-04 NULL NULL NULL NULL NULL NULL NULL NULL 2007-05-05 NULL NULL NULL NULL NULL NULL NULL NULL 2007-05-02 NULL NULL 2 107.500000000 NULL NULL 1 11.100000000 2007-05-03 NULL NULL 1 100.000000000 1 5.500000000 2 200.000000000 2007-05-04 NULL NULL NULL NULL 1 3.500000000 NULL NULL 2007-05-05 3 35.600000000 NULL NULL NULL NULL NULL NULL //----------

kukow · 2008-03-15

use tempdb go create table T1(id int,name varchar(4),kind bit,start_time varchar(5),end_time varchar(5)) insert T1 select 1,'早',0,'07:01','10:00' union all select 2,'中',0,'10:01','15:00' union all select 3,'晚',0,'15:01','22:00' union all select 4,'夜',1,'22:01','07:00'   --(kind 为1表示跨天) create table T2(id int identity(1,1),dt datetime,amount money) insert t2 select '2007-05-02 10:34:10',7.5 union all select '2007-05-03 03:10:10',11.1 union all select '2007-05-03 15:12:11',5.5 union all select '2007-05-04 19:10:10',3.5 select *     ,合计次数 = [次数(早)]+[次数(中)]+[次数(晚)]+[次数(夜)]     ,合计金额 = [金额(早)]+[金额(中)]+[金额(晚)]+[金额(夜)] from (     select date         ,sum(case when name='早' then 1 else 0 end) as [次数(早)]         ,sum(case when name='早' then amount else 0 end) as [金额(早)]         ,sum(case when name='中' then 1 else 0 end) as [次数(中)]         ,sum(case when name='中' then amount else 0 end) as [金额(中)]         ,sum(case when name='晚' then 1 else 0 end) as [次数(晚)]         ,sum(case when name='晚' then amount else 0 end) as [金额(晚)]         ,sum(case when name='夜' then 1 else 0 end) as [次数(夜)]           ,sum(case when name='夜' then amount else 0 end) as [金额(夜)]     from (    select convert(varchar(10),case  when t1.kind=1 and convert(varchar(5),t2.dt,8)<=t1.end_time then dateadd(day,-1,t2.dt) else t2.dt end,120) date                 ,amount,name             from t1 ,t2             where 1=                 case t1.kind when 0 then (case when convert(varchar(5),t2.dt,8) between t1.start_time and t1.end_time then 1 else 0 end)                       else (case when convert(varchar(5),t2.dt,8) >= t1.start_time or convert(varchar(5),t2.dt,8)<=t1.end_time then 1 else 0 end)  end         ) a     group by date ) m 问题自己解决了,大富翁现在人气越来越谈了.结贴了...

跪求跨天查询的合理解决方案(150分)

kukow

Unregistered / Unconfirmed

kukow

Unregistered / Unconfirmed

sunnyfairy

Unregistered / Unconfirmed

sunnyfairy

Unregistered / Unconfirmed

kukow

Unregistered / Unconfirmed

Similar threads