首页 新闻 搜索 专区 学院

求一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
提问于:2010-08-16 19:42
< >
分享
所有回答(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

 

修改了下,原来的走了弯路。

邀月 | 园豆:25375 (高人七级) | 2010-08-16 20:52
select t.*,isnull(r.拨入,0) as 拨入,isnull(c.拨出,0)as 拨出 怎么区别啊。 。 他们是一个字段。
支持(0) 反对(0) 我没装.. | 园豆:5 (初学一级) | 2010-08-16 21:16
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

 

killkill | 园豆:992 (小虾三级) | 2010-08-16 23:44
学习
支持(0) 反对(0) 邀月 | 园豆:25375 (高人七级) | 2010-08-17 00:00
0

stewen | 园豆:14 (初学一级) | 2010-08-17 09:23
0

看到高手出招,就没敢出手

changbluesky | 园豆:854 (小虾三级) | 2010-08-20 15:51
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

John29 | 园豆:825 (小虾三级) | 2010-09-10 15:57
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册