求一sql统计报表语句。
0
悬赏园豆：50
[待解决问题]
求SQL查询，环境：sql server 2005
数据库： 表名：table
Id 插入时间（Time） 通话类型（Type） 电话号码
1 2009-07-07 拨入 12345678
2 2009-07-07 拨入 78945612
3 2009-07-08 拨入 12345678
4 2009--7-08 拨出 12345678
5 2009-07-09 拨出 12345678
6 2009-08-01 拨入 12345678
7 2009-08-01 拨出 12345678
8 2009-08-02 拨入 41258789
问题补充：
条件:查询2009-07-01到2009-07-30之间的电话量.
显示格式如下:
时间 总通话量 拨入电话 拨出电话
2007-11-01 3 3 0
2007-11-02 2 0 2
2007-11-03 1 1 0
2007-12-01 1 1 0
一条sql语句实现
所有回答(5)
0
IF NOT OBJECT_ID('[tbTel]') IS NULL
DROP TABLE [tbTel]
GO
CREATE TABLE [tbTel]
([ID] int identity(1,1) primary key not null,
AddTime dateTime null,
[Type] Nvarchar(10) null,
TelCode Nvarchar(20) null
)
go
INSERT [tbTel]
SELECT '2009-07-07','拨入','12345678' union all
SELECT '2009-07-07','拨入','78945612' union all
SELECT '2009-07-08','拨入','12345678' union all
SELECT '2009-07-08','拨出','12345678' union all
SELECT '2009-07-09','拨出','12345678' union all
SELECT '2009-08-01','拨入','12345678' union all
SELECT '2009-08-01','拨出','12345678' union all
SELECT '2009-08-02','拨入','41258789'
go
select convert(nvarchar(10),addtime,120) as 通话时间
,COUNT(1) as 通话总数
,COUNT(case when [type]='拨入' then 1 else null end) 拨入电话
,COUNT(case when [type]='拨出' then 1 else null end) 拨出电话
from tbTel
where AddTime between '2009-07-07' and '2009-08-02'
group by convert(nvarchar(10),addtime,120)
--通话时间 通话总数 拨入电话 拨出电话
--2009-07-07 2 2 0
--2009-07-08 2 1 1
--2009-07-09 1 0 1
--2009-08-01 2 1 1
--2009-08-02 1 1 0
修改了下，原来的走了弯路。
select t.*,isnull(r.拨入,0) as 拨入,isnull(c.拨出,0)as 拨出 怎么区别啊。 。
他们是一个字段。
0
不知道LZ的数据是否会出现某一天没有数据的情况呢，例如2007-08-15 一个电话都没有拨出或者拨入，如果存在这种情况的话，报表极有可能会“缺项”，我写了个不怕这种“缺项”的：
--- 原始数据
with tmp(id,time,call_type,telcode) as (
SELECT 1,'2009-07-07','拨入','12345678' union all
SELECT 2,'2009-07-07','拨入','78945612' union all
SELECT 3,'2009-07-08','拨入','12345678' union all
SELECT 4,'2009-07-08','拨出','12345678' union all
SELECT 5,'2009-07-09','拨出','12345678' union all
SELECT 6,'2009-08-01','拨入','12345678' union all
SELECT 7,'2009-08-01','拨出','12345678' union all
SELECT 8,'2009-08-02','拨入','41258789'
),
--- 开始
--- 时间的维度
dim_time(time) as (
select SUBSTRING(CONVERT(varchar,time,120),1,10) time
from (
select CAST('2009-07-01' as datetime)-1
-- ^^^^^^^^^^ 填入开始时间
+ROW_NUMBER() over(order by OBJECT_ID) time
from sys.objects
) a
where a.time<='2009-07-30'
-- ^^^^^^^^^^ 填入结束时间
)
select
d.time 时间 ,
count(call_type) 总通话量 ,
COUNT(case when call_type='拨入' then 1 else null end) 拨入电话 ,
COUNT(case when call_type='拨出' then 1 else null end) 拨出电话
from tmp t
right outer join dim_time d
on t.time = d.time
group by d.time
order by 1
-- 以下是结果
时间 总通话量 拨入电话 拨出电话
-------------------- ----------- ----------- -----------
2009-07-01 0 0 0
2009-07-02 0 0 0
2009-07-03 0 0 0
2009-07-04 0 0 0
2009-07-05 0 0 0
2009-07-06 0 0 0
2009-07-07 2 2 0
2009-07-08 2 1 1
2009-07-09 1 0 1
2009-07-10 0 0 0
2009-07-11 0 0 0
2009-07-12 0 0 0
2009-07-13 0 0 0
2009-07-14 0 0 0
2009-07-15 0 0 0
2009-07-16 0 0 0
2009-07-17 0 0 0
2009-07-18 0 0 0
2009-07-19 0 0 0
2009-07-20 0 0 0
2009-07-21 0 0 0
2009-07-22 0 0 0
2009-07-23 0 0 0
2009-07-24 0 0 0
2009-07-25 0 0 0
2009-07-26 0 0 0
2009-07-27 0 0 0
2009-07-28 0 0 0
2009-07-29 0 0 0
2009-07-30 0 0 0
学习
0
顶
0
看到高手出招,就没敢出手
0
整这么复杂干吗
select time,count(*),
sum(case type when '拨入' then 1 else 0 end),
sum(case type when '拨出' then 1 else 0 end)
from table
group by time