表 A
--------------------------------------------
A_ID A_Number A_StartTime A_EndTime
101 1 2:00 3:00
101 1 5:00 6:00
102 2 8:00 9:00
--------------------------------------------
表 B
-------------------------------------------
B_Number B_Time
1 1:00
2 5:00
2 7:00
-------------------------------------------
表 C
-------------------------------------------
C_ID C_Name
101 电信
102 移动
------------------------------------------- 要求得到统计记录如下
(给定时间区间 [1:00,4:00]
A_StartTime,A_EndTime,B_Time 在 [1:00,4:00]区间内
(A_Number = B_Number, A_ID = C_ID) Group by A_ID
--------------------------------------------------
Name ID Count_A (A记录数) Count_B (B的记录数)
电信 101 1 1
移动 102 0 0
(汇总) 1 1
---------------------------------------------------
--------------------------------------------
A_ID A_Number A_StartTime A_EndTime
101 1 2:00 3:00
101 1 5:00 6:00
102 2 8:00 9:00
--------------------------------------------
表 B
-------------------------------------------
B_Number B_Time
1 1:00
2 5:00
2 7:00
-------------------------------------------
表 C
-------------------------------------------
C_ID C_Name
101 电信
102 移动
------------------------------------------- 要求得到统计记录如下
(给定时间区间 [1:00,4:00]
A_StartTime,A_EndTime,B_Time 在 [1:00,4:00]区间内
(A_Number = B_Number, A_ID = C_ID) Group by A_ID
--------------------------------------------------
Name ID Count_A (A记录数) Count_B (B的记录数)
电信 101 1 1
移动 102 0 0
(汇总) 1 1
---------------------------------------------------
解决方案 »
- bcp里面的format文件生成的问题
- 求助:如何修改数据库数据
- 关于在聚合中包含子查询的问题,有什么方法解决吗?
- Microsoft SQL Server 2005 安装错误,紧急求助,谢谢啊~~~~~错误为: [Microsoft][SQL Native Client]共享内存提供程序: 超时错误 [258]
- 【【谁能告诉我SQL中怎么按时间段求平均值】】
- 求SQL
- 如何修改SQL语句,实现
- 哪里有SQL Server 2000的学习教程(电子文档)可以免费下载?
- sql语句的问题
- HTTP Status 500 大神求解
- 取随机数据,急啊!!
- 【急】求一update Sql语句,解决马上结贴给分!谢谢
insert A select '101', 1, '2:00', '3:00'
union all select '101', 1, '5:00', '6:00'
union all select '102', 2, '8:00', '9:00'
create table B(B_Number int, B_Time varchar(10))
insert B select 1, '1:00'
union all select 2, '5:00'
union all select 2, '7:00'
create table C(C_ID varchar(10), C_Name nvarchar(10))
insert C select '101', '电信'
union all select '102', '移动' declare @dt1 datetime, @dt2 datetime
select @dt1=convert(datetime, '1:00', 108), @dt2= convert(datetime, '4:00', 108)select C.*,Count_A=isnull(A.Count_A, 0), Count_B=isnull(B.Count_B, 0) from C
left join
(
select A_ID, A_Number, Count_A=count(*) from A
where convert(datetime, A_StartTime, 108) between @dt1 and @dt2 and
convert(datetime, A_EndTime, 108) between @dt1 and @dt2
group by A_ID, A_Number
) A on C.C_ID=A.A_ID
left join
(
select B_Number, Count_B=count(*) from B
where convert(datetime, B_Time, 108) between @dt1 and @dt2
group by B_Number
) B on A.A_Number=B.B_Number--result
C_ID C_Name Count_A Count_B
---------- ---------- ----------- -----------
101 电信 1 1
102 移动 0 0(2 row(s) affected)
insert @ta select '101', 1, '2:00', '3:00'
union all select '101', 1, '5:00', '6:00'
union all select '102', 2, '8:00', '9:00'
declare @tb table (B_Number int, B_Time varchar(10))
insert @tb select 1, '1:00'
union all select 2, '5:00'
union all select 2, '7:00'
declare @tc table (C_ID varchar(10), C_Name nvarchar(10))
insert @tc select '101', '电信'
union all select '102', '移动' select name=max(C_Name),
ID=case when GROUPING(C_ID)=0 then C_ID else '汇总' end,
[Count_A (A记录数)]= sum(case when DATEPART(hh, A_StartTime) between 1 and 4
and DATEPART(hh, A_EndTime) between 1 and 4 then 1 else 0 end),
[Count_B (B的记录数)]=sum(case when DATEPART(hh, B_Time) between 1 and 4 then 1 else 0 end)
from @ta a,@tb b,@tc c
where A_Number = B_Number and A_ID = C_ID
group by C_ID,C_Name WITH ROLLUP(所影响的行数为 2 行)name ID Count_A (A记录数) Count_B (B的记录数)
---------- ---------- -------------- ---------------
电信 101 1 2
电信 101 1 2
移动 102 0 0
移动 102 0 0
移动 汇总 1 2(所影响的行数为 5 行)
insert @ta select '101', 1, '2:00', '3:00'
union all select '101', 1, '5:00', '6:00'
union all select '102', 2, '8:00', '9:00'
declare @tb table (B_Number int, B_Time varchar(10))
insert @tb select 1, '1:00'
union all select 2, '5:00'
union all select 2, '7:00'
declare @tc table (C_ID varchar(10), C_Name nvarchar(10))
insert @tc select '101', '电信'
union all select '102', '移动' select name=max(C_Name),
ID=case when GROUPING(C_ID)=0 then C_ID else '汇总' end,
[Count_A (A记录数)]= sum(case when DATEPART(hh, A_StartTime) between 1 and 4
and DATEPART(hh, A_EndTime) between 1 and 4 then 1 else 0 end),
[Count_B (B的记录数)]=sum(case when DATEPART(hh, B_Time) between 1 and 4 then 1 else 0 end)
from @ta a,@tb b,@tc c
where A_Number = B_Number and A_ID = C_ID
group by C_ID WITH ROLLUP--去掉C_Name
(所影响的行数为 2 行)name ID Count_A (A记录数) Count_B (B的记录数)
---------- ---------- -------------- ---------------
电信 101 1 2
移动 102 0 0
移动 汇总 1 2(所影响的行数为 3 行)
insert into @A select 101,1,'2:00','3:00'
insert into @A select 101,1,'5:00','6:00'
insert into @A select 102,2,'8:00','9:00'declare @B table(B_Number int,B_Time varchar(10))
insert into @B select 1,'1:00'
insert into @B select 2,'5:00'
insert into @B select 2,'7:00' declare @C table(C_ID int,C_Name varchar(10))
insert into @C select 101,'电信'
insert into @C select 102,'移动' declare @Date1 varchar(10),@Date2 varchar(10)
set @Date1='1:00'
set @Date2='4:00'
select
isnull(n.C_Name,'合计') as C_Name,
n.C_ID,
isnull(count(m.A_ID),0) as Count_A,
isnull(sum(m.Count_B),0) as Count_B
from
@c n
left join
(select
a.*,
(select count(1) from @B where B_Number=a.A_Number and B_Time between @Date1 and @Date2) as Count_B
from
@A a
where
(A_StartTime between @Date1 and @Date2)
and
(A_EndTime between @Date1 and @Date2)) m
on
n.C_ID=m.A_ID
group by
n.C_Name,n.C_ID
with rollup having grouping(C_ID)=0 or grouping(C_Name)=1/*
C_Name C_ID Count_A Count_B
---------- ----------- ----------- -----------
电信 101 1 1
移动 102 0 0
合计 NULL 1 1
*/
select c.c_id ,
c.c_name ,
(
select count(1) from a , b
where a.a_id = c.c_id
and a.A_StartTime between '1:00' and '4:00'
and a.A_EndTime between '1:00' and '4:00'
and a.a_number = b.b_number
and b.B_Time between '1:00' and '4:00'
) as A_Number ,
(
select count(1) from a , b
where a.a_id = c.c_id
and a.A_StartTime between '1:00' and '4:00'
and a.A_EndTime between '1:00' and '4:00'
and a.a_number = b.b_number
and b.B_Time between '1:00' and '4:00'
) as B_Number
from c
Union all select '' ,
'汇总',
count(1) ,
count(1)
from a , b
where
a.A_StartTime between '1:00' and '4:00'
and a.A_EndTime between '1:00' and '4:00'
and a.a_number = b.b_number
and b.B_Time between '1:00' and '4:00'
-------------------------------------
B_Number = A_Number
B_Time 在 [1:00,4:00]区间内
Group by A_ID
主要是这一段是关键
with rollup having grouping(C_ID)=0 or grouping(C_Name)=1
用我的判断就行了
DATEPART(hh, A_StartTime) between 1 and 4