帮忙统计一下,谢谢了~~~~

Question

编号              激活码                    激活状态   媒体类型        时间
29 35y23mp38g67 1 1001 2010-03-23 09:40:04.583
30 3fk7tyr2m4pm 1 1001 2010-03-23 09:40:04.583
53 jx2v8aega2zp 0 1001 2010-03-23 09:40:04.583
54 pcdvk8yepke8 0 1001 2010-03-23 09:40:04.583
55 9znasrbs9rz6 0 1001 2010-03-23 09:40:04.583
56 snf25fzvdrac 1 1001 2010-03-24 09:40:04.583
57 wmqqkq5pttn2 0 1002 2010-03-24 09:40:04.583
58 ym2c2ddhx79k 0 1002 2010-03-24 09:40:04.583
59 xji36za9rmu2 0 1002 2010-03-25 09:40:04.583
60 4j6ukhn8afv6 1 1002 2010-03-25 09:40:04.583
61 zmviv546xaw4 0 1003 2010-03-25 09:40:04.583
62 bgx93m5g4hng 1 1003 2010-03-25 09:40:04.583
63 bgx93m5g4h34 0 1003 2010-03-26 09:40:04.583
64 bgx93m5g4h22 1 1003 2010-03-26 09:40:04.583
65 bgx93m5344ng 0 1003 2010-03-26 09:40:04.583
根据时间,类型,状态去统计,统计结果如下媒体类型        时间             激活码总数 已激活  未激活    激活率
1001    2010-03-23 09:40:04.583 4 2 2 50%
1001    2010-03-24 09:40:04.583 1 1 0 100%
1002    2010-03-24 09:40:04.583 2 0 2 0%
1002    2010-03-25 09:40:04.583 2 1 1 50%
1003    2010-03-25 09:40:04.583      2        1        1        50%
1003    2010-03-26 09:40:04.583      3        1        2        33.3%

Answer 1

select
  媒体类型,时间,
  count(1) as 激活码总数,
  sum(case 激活状态 when 1 then 1 else 0 end) as 已激活,
  sum(case 激活状态 when 0 then 1 else 0 end) as 未激活,
  cast(sum(case 激活状态 when 1 then 1 else 0 end)*100.0/count(1) as dec(18,1))+'%' as 激活率
from
  tb
group by
  媒体类型,时间

Answer 2

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (编号 int,激活码 nvarchar(24),激活状态 int,媒体类型 int,时间 datetime)
insert into [tb]
select 29,'35y23mp38g67',1,1001,'2010-03-23 09:40:04.583' union all
select 30,'3fk7tyr2m4pm',1,1001,'2010-03-23 09:40:04.583' union all
select 53,'jx2v8aega2zp',0,1001,'2010-03-23 09:40:04.583' union all
select 54,'pcdvk8yepke8',0,1001,'2010-03-23 09:40:04.583' union all
select 55,'9znasrbs9rz6',0,1001,'2010-03-23 09:40:04.583' union all
select 56,'snf25fzvdrac',1,1001,'2010-03-24 09:40:04.583' union all
select 57,'wmqqkq5pttn2',0,1002,'2010-03-24 09:40:04.583' union all
select 58,'ym2c2ddhx79k',0,1002,'2010-03-24 09:40:04.583' union all
select 59,'xji36za9rmu2',0,1002,'2010-03-25 09:40:04.583' union all
select 60,'4j6ukhn8afv6',1,1002,'2010-03-25 09:40:04.583' union all
select 61,'zmviv546xaw4',0,1003,'2010-03-25 09:40:04.583' union all
select 62,'bgx93m5g4hng',1,1003,'2010-03-25 09:40:04.583' union all
select 63,'bgx93m5g4h34',0,1003,'2010-03-26 09:40:04.583' union all
select 64,'bgx93m5g4h22',1,1003,'2010-03-26 09:40:04.583' union all
select 65,'bgx93m5344ng',0,1003,'2010-03-26 09:40:04.583'
select 媒体类型,
       时间,
       count(*)总数,
       sum(case when 激活状态 =1 then 1 else 0 end)已激活,
       sum(case when 激活状态 =0 then 1 else 0 end)未激活,
       ltrim(cast(sum(case when 激活状态 =1 then 1 else 0 end)*100.0/count(*)as dec(18,2)))+'%'
from tb 
group by 媒体类型,时间
/*
媒体类型        时间                      总数          已激活         未激活         
----------- ----------------------- ----------- ----------- ----------- ------------------------------------------
1001        2010-03-23 09:40:04.583 5           2           3           40.00%
1001        2010-03-24 09:40:04.583 1           1           0           100.00%
1002        2010-03-24 09:40:04.583 2           0           2           0.00%
1002        2010-03-25 09:40:04.583 2           1           1           50.00%
1003        2010-03-25 09:40:04.583 2           1           1           50.00%
1003        2010-03-26 09:40:04.583 3           1           2           33.33%(6 個資料列受到影響)
*/
           

Answer 3

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-03-26 10:43:29
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] INT,[激活码] NVARCHAR(20),[激活状态] INT,[媒体类型] INT,[时间] DATETIME)
INSERT [tb]
SELECT 29,'35y23mp38g67',1,1001,N'2010-03-23 09:40:04.583' UNION ALL
SELECT 30,'3fk7tyr2m4pm',1,1001,N'2010-03-23 09:40:04.583' UNION ALL
SELECT 53,'jx2v8aega2zp',0,1001,N'2010-03-23 09:40:04.583' UNION ALL
SELECT 54,'pcdvk8yepke8',0,1001,N'2010-03-23 09:40:04.583' UNION ALL
SELECT 55,'9znasrbs9rz6',0,1001,N'2010-03-23 09:40:04.583' UNION ALL
SELECT 56,'snf25fzvdrac',1,1001,N'2010-03-24 09:40:04.583' UNION ALL
SELECT 57,'wmqqkq5pttn2',0,1002,N'2010-03-24 09:40:04.583' UNION ALL
SELECT 58,'ym2c2ddhx79k',0,1002,N'2010-03-24 09:40:04.583' UNION ALL
SELECT 59,'xji36za9rmu2',0,1002,N'2010-03-25 09:40:04.583' UNION ALL
SELECT 60,'4j6ukhn8afv6',1,1002,N'2010-03-25 09:40:04.583' UNION ALL
SELECT 61,'zmviv546xaw4',0,1003,N'2010-03-25 09:40:04.583' UNION ALL
SELECT 62,'bgx93m5g4hng',1,1003,N'2010-03-25 09:40:04.583' UNION ALL
SELECT 63,'bgx93m5g4h34',0,1003,N'2010-03-26 09:40:04.583' UNION ALL
SELECT 64,'bgx93m5g4h22',1,1003,N'2010-03-26 09:40:04.583' UNION ALL
SELECT 65,'bgx93m5344ng',0,1003,N'2010-03-26 09:40:04.583'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select [媒体类型],时间,激活码总数=COUNT(1),
已激活=SUM([激活状态]),未激活=COUNT(1)-SUM([激活状态]), 激活率=ltrim(CAST(SUM([激活状态])*100./COUNT(1) as float))+'%'
from tb 
group by [媒体类型],时间
/*
媒体类型        时间                      激活码总数       已激活         未激活         激活率
----------- ----------------------- ----------- ----------- ----------- ------------------------
1001        2010-03-23 09:40:04.583 5           2           3           40%
1001        2010-03-24 09:40:04.583 1           1           0           100%
1002        2010-03-24 09:40:04.583 2           0           2           0%
1002        2010-03-25 09:40:04.583 2           1           1           50%
1003        2010-03-25 09:40:04.583 2           1           1           50%
1003        2010-03-26 09:40:04.583 3           1           2           33.3333%(6 行受影响)
*/

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-03-26 10:47:47
-- Verstion:
--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 
-- May 26 2009 14:24:20 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([编号] int,[激活码] varchar(12),[激活状态] int,[媒体类型] int,[时间] datetime)
insert [tb]
select 29,'35y23mp38g67',1,1001,'2010-03-23 09:40:04.583' union all
select 30,'3fk7tyr2m4pm',1,1001,'2010-03-23 09:40:04.583' union all
select 53,'jx2v8aega2zp',0,1001,'2010-03-23 09:40:04.583' union all
select 54,'pcdvk8yepke8',0,1001,'2010-03-23 09:40:04.583' union all
select 55,'9znasrbs9rz6',0,1001,'2010-03-23 09:40:04.583' union all
select 56,'snf25fzvdrac',1,1001,'2010-03-24 09:40:04.583' union all
select 57,'wmqqkq5pttn2',0,1002,'2010-03-24 09:40:04.583' union all
select 58,'ym2c2ddhx79k',0,1002,'2010-03-24 09:40:04.583' union all
select 59,'xji36za9rmu2',0,1002,'2010-03-25 09:40:04.583' union all
select 60,'4j6ukhn8afv6',1,1002,'2010-03-25 09:40:04.583' union all
select 61,'zmviv546xaw4',0,1003,'2010-03-25 09:40:04.583' union all
select 62,'bgx93m5g4hng',1,1003,'2010-03-25 09:40:04.583' union all
select 63,'bgx93m5g4h34',0,1003,'2010-03-26 09:40:04.583' union all
select 64,'bgx93m5g4h22',1,1003,'2010-03-26 09:40:04.583' union all
select 65,'bgx93m5344ng',0,1003,'2010-03-26 09:40:04.583'
--------------开始查询--------------------------
select
  媒体类型,时间,
  count(1) as 激活码总数,
  sum(case 激活状态 when 1 then 1 else 0 end) as 已激活,
  sum(case 激活状态 when 0 then 1 else 0 end) as 未激活,
  ltrim(cast(sum(case 激活状态 when 1 then 1 else 0 end)*100.0/count(1) as dec(18,1)))+'%' as 激活率
from
  tb
group by
  媒体类型,时间
----------------结果----------------------------
/* 媒体类型        时间                      激活码总数       已激活         未激活         激活率
----------- ----------------------- ----------- ----------- ----------- ------------------------------------------
1001        2010-03-23 09:40:04.583 5           2           3           40.0%
1001        2010-03-24 09:40:04.583 1           1           0           100.0%
1002        2010-03-24 09:40:04.583 2           0           2           0.0%
1002        2010-03-25 09:40:04.583 2           1           1           50.0%
1003        2010-03-25 09:40:04.583 2           1           1           50.0%
1003        2010-03-26 09:40:04.583 3           1           2           33.3%(6 行受影响)*/

Answer 5

-->SQL查询如下:
select [媒体类型],时间,激活码总数=COUNT(1),
已激活=SUM([激活状态]),未激活=COUNT(1)-SUM([激活状态]), 激活率=ltrim(CAST(cast(SUM([激活状态])*100./COUNT(1)as DEC(10,1)) as float))+'%'
from tb 
group by [媒体类型],时间
/*
媒体类型        时间                      激活码总数       已激活         未激活         激活率
----------- ----------------------- ----------- ----------- ----------- ------------------------
1001        2010-03-23 09:40:04.583 5           2           3           40%
1001        2010-03-24 09:40:04.583 1           1           0           100%
1002        2010-03-24 09:40:04.583 2           0           2           0%
1002        2010-03-25 09:40:04.583 2           1           1           50%
1003        2010-03-25 09:40:04.583 2           1           1           50%
1003        2010-03-26 09:40:04.583 3           1           2           33.3%(6 行受影响)
*/这是修饰百分比后的效果.

Answer 6

---try
declare @tb table (编号 int,激活码 nvarchar(50),激活状态 int,媒体类型 int,时间 datetime)
insert into @tb select 29,'35y23mp38g67',1,1001,'2010-03-23 09:40:04.583'
      union all select 30,'3fk7ty42m4pm',1,1001,'2010-03-23 09:40:04.583'
      union all select 53,'jx2v8aega2zp',0,1001,'2010-03-23 09:40:04.583'
      union all select 54,'pcdvk8yepke8',0,1001,'2010-03-23 09:40:04.583'
      union all select 55,'8znasrbs9rz6',0,1001,'2010-03-23 09:40:04.583'
      union all select 56,'snf25fzvdrac',1,1001,'2010-03-24 09:40:04.583'
      union all select 57,'wmqqkq5pttn2',0,1002,'2010-03-24 09:40:04.583'
      union all select 58,'ym2c2ddhx79k',0,1002,'2010-03-24 09:40:04.583'
      union all select 59,'xji36za9rmu2',0,1002,'2010-03-25 09:40:04.583'
      union all select 60,'4j6ukhn8afv6',1,1002,'2010-03-25 09:40:04.583'
      union all select 61,'zmviv546xaw4',0,1003,'2010-03-25 09:40:04.583'
      union all select 62,'bgx93m5g4hng',1,1003,'2010-03-25 09:40:04.583'
      union all select 63,'bgx93m5g4h34',0,1003,'2010-03-26 09:40:04.583'
      union all select 64,'bgx93m5g4h22',1,1003,'2010-03-26 09:40:04.583'
      union all select 65,'bgx93m5344ng',0,1003,'2010-03-26 09:40:04.583'
select 媒体类型,时间,激活码总数=count(*),已激活= sum(case when 激活状态=1 then 1 else 0 end),
       未激活=sum(case when 激活状态=0 then 1 else 0 end),
       激活率= str(sum(case when 激活状态=1 then 1 else 0 end)*1.0/count(*)*100)+'%' 
       from @tb group by 媒体类型,时间(15 行受影响)
媒体类型        时间                      激活码总数       已激活         未激活         激活率
----------- ----------------------- ----------- ----------- ----------- -----------
1001        2010-03-23 09:40:04.583 5           2           3                   40%
1001        2010-03-24 09:40:04.583 1           1           0                  100%
1002        2010-03-24 09:40:04.583 2           0           2                    0%
1002        2010-03-25 09:40:04.583 2           1           1                   50%
1003        2010-03-25 09:40:04.583 2           1           1                   50%
1003        2010-03-26 09:40:04.583 3           1           2                   33%(6 行受影响)

Answer 7

--trydeclare @tb table (编号 int,激活码 nvarchar(50),激活状态 int,媒体类型 int,时间 datetime)
insert into @tb select 29,'35y23mp38g67',1,1001,'2010-03-23 09:40:04.583'
      union all select 30,'3fk7ty42m4pm',1,1001,'2010-03-23 09:40:04.583'
      union all select 53,'jx2v8aega2zp',0,1001,'2010-03-23 09:40:04.583'
      union all select 54,'pcdvk8yepke8',0,1001,'2010-03-23 09:40:04.583'
      union all select 55,'8znasrbs9rz6',0,1001,'2010-03-23 09:40:04.583'
      union all select 56,'snf25fzvdrac',1,1001,'2010-03-24 09:40:04.583'
      union all select 57,'wmqqkq5pttn2',0,1002,'2010-03-24 09:40:04.583'
      union all select 58,'ym2c2ddhx79k',0,1002,'2010-03-24 09:40:04.583'
      union all select 59,'xji36za9rmu2',0,1002,'2010-03-25 09:40:04.583'
      union all select 60,'4j6ukhn8afv6',1,1002,'2010-03-25 09:40:04.583'
      union all select 61,'zmviv546xaw4',0,1003,'2010-03-25 09:40:04.583'
      union all select 62,'bgx93m5g4hng',1,1003,'2010-03-25 09:40:04.583'
      union all select 63,'bgx93m5g4h34',0,1003,'2010-03-26 09:40:04.583'
      union all select 64,'bgx93m5g4h22',1,1003,'2010-03-26 09:40:04.583'
      union all select 65,'bgx93m5344ng',0,1003,'2010-03-26 09:40:04.583'
select 媒体类型,时间,激活码总数=count(*),已激活= sum(case when 激活状态=1 then 1 else 0 end),
       未激活=sum(case when 激活状态=0 then 1 else 0 end),
       激活率= str(sum(case when 激活状态=1 then 1 else 0 end)*1.0/count(*)*100,5,2)+'%' 
       from @tb group by 媒体类型,时间(15 行受影响)
媒体类型        时间                      激活码总数       已激活         未激活         激活率
----------- ----------------------- ----------- ----------- ----------- ------
1001        2010-03-23 09:40:04.583 5           2           3           40.00%
1001        2010-03-24 09:40:04.583 1           1           0           100.0%
1002        2010-03-24 09:40:04.583 2           0           2            0.00%
1002        2010-03-25 09:40:04.583 2           1           1           50.00%
1003        2010-03-25 09:40:04.583 2           1           1           50.00%
1003        2010-03-26 09:40:04.583 3           1           2           33.33%(6 行受影响