100分：求助一个报表查询SQL语句
有如下一组数据：表 Demo
id名字任务开始日期任务截至日期任务完成日期任务状态
1hbwang2012-7-11 0:002012-7-18 0:002012-7-17 0:00正常关闭
2alma2012-7-12 0:002012-7-19 0:002012-7-18 0:00正常关闭
3hbwang2012-7-13 0:002012-7-20 0:002012-7-21 0:00正常关闭
4alma2012-7-14 0:002012-7-21 0:002012-7-21 0:00正常关闭
5alma2012-7-15 0:002012-7-22 0:00 异常关闭
6hbwang2012-7-16 0:002012-7-23 0:00
7alma2012-7-17 0:002012-7-24 0:002012-7-24 0:00正常关闭
8jjpan2012-7-18 0:002012-7-25 0:002012-7-25 0:00正常关闭
9jjpan2012-7-19 0:002012-7-26 0:002012-7-27 0:00正常关闭
10jjpan2012-7-20 0:002012-7-27 0:00
11hbwang2012-7-21 0:002012-7-28 0:002012-7-27 0:00正常关闭
想得到如下报表
查询条件根据任务日期2012-7-01~2012-7-31
名字任务数完成数未完成数 异常关闭数逾期数完成率逾期率
hbwang 4.00 3.00 1.00 1.00 75%33.33%
alma 4.00 3.00 1.00 1.00 75%
jjpan 3.00 2.00 1.00 1.00 66.67%50%
栏目说明：按名字分组
任务数（对应名字的任务总数）
完成数（任务状态为正常关闭）
未完成数（任务状态为异常关闭或任务完成日为空期）
异常关闭数（任务状态为任务完成日期）
逾期数（任务完成日期大于任务截止日期）
完成率（完成数/任务数）
逾期率（逾期数/完成数）
[解决办法]
- SQL code
select [名字], count(1)over(partition by [名字]) as 任务数, SUM(case when [任务状态]='正常关闭' then 1 else 0 end) as 完成数, SUM(case when [任务状态]='异常关闭' or [任务完成日期] is null then 1 else 0 end) as 未完成数, SUM(case when 任务状态=[任务完成日期] then 1 else 0 end) as 异常关闭数, SUM(case when 任务完成日期>任务截止日期 then 1 else 0 end) as 逾期数, ltrim(SUM(case when [任务状态]='正常关闭' then 1 else 0 end)*100.0/count(1)over(partition by [名字]))+'%'as 完成率, ltrim(SUM(case when 任务完成日期>任务截止日期 then 1 else 0 end)*100.0/SUM(case when [任务状态]='正常关闭' then 1 else 0 end)) as 逾期率from [Demo]group by [名字] --应该是对的
[解决办法]
行转列。。。 日经帖。
[解决办法]
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-07-18 13:01:26-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[demo]if object_id('[demo]') is not null drop table [demo]go create table [demo]([id] int,[名字] varchar(6),[任务开始日期] datetime,[任务截至日期] datetime,[任务完成日期] varchar(14),[任务状态] varchar(8))insert [demo]select 1,'hbwang','2012-7-11 0:00','2012-7-18 0:00','2012-7-17 0:00','正常关闭' union allselect 2,'alma','2012-7-12 0:00','2012-7-19 0:00','2012-7-18 0:00','正常关闭' union allselect 3,'hbwang','2012-7-13 0:00','2012-7-20 0:00','2012-7-21 0:00','正常关闭' union allselect 4,'alma','2012-7-14 0:00','2012-7-21 0:00','2012-7-21 0:00','正常关闭' union allselect 5,'alma','2012-7-15 0:00','2012-7-22 0:00',null,'异常关闭'union allselect 6,'hbwang','2012-7-16 0:00','2012-7-23 0:00',null,null union allselect 7,'alma','2012-7-17 0:00','2012-7-24 0:00','2012-7-24 0:00','正常关闭' union allselect 8,'jjpan','2012-7-18 0:00','2012-7-25 0:00','2012-7-25 0:00','正常关闭' union allselect 9,'jjpan','2012-7-19 0:00','2012-7-26 0:00','2012-7-27 0:00','正常关闭' union allselect 10,'jjpan','2012-7-20 0:00','2012-7-27 0:00',null,null union allselect 11,'hbwang','2012-7-21 0:00','2012-7-28 0:00','2012-7-27 0:00','正常关闭'--------------开始查询--------------------------select [名字], count(1) as 任务数, sum(case [任务状态] when '正常关闭' then 1 else 0 end) as 完成数, sum(case when [任务状态]<>'正常关闭' then 1 else 0 end) as 未完成数, sum(case [任务状态] when '异常关闭' then 1 else 0 end) as 异常关闭数, sum(case when [任务完成日期]>[任务截至日期] then 1 else 0 end) as 逾期数, ltrim(cast(sum(case when [任务状态]='正常关闭' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%'as 完成率, ltrim(cast(sum(case when 任务完成日期>任务截至日期 then 1 else 0 end)*100.0/SUM(case when [任务状态]='正常关闭' then 1 else 0 end) as dec(18,2))) as 逾期率from [Demo]group by [名字] ----------------结果----------------------------/* 名字 任务数 完成数 未完成数 异常关闭数 逾期数 完成率 逾期率------ ----------- ----------- ----------- ----------- ----------- ------------------------------------------ -----------------------------------------alma 4 3 1 1 0 75.00% 0.00hbwang 4 3 0 0 1 75.00% 33.33jjpan 3 2 0 0 1 66.67% 50.00(3 行受影响)*/