求段统计SQL

Question

任务表：
taskID(int,PrimaryKey)
GroupID(int32)
CreationDate(DateTime)
Creator(NVarchar(50))
MainIndicator(bit)
StatusCode(int)其中：
taskid为主键，groupid为该任务所属任务组的id,MainIndicator用以确定该task是不是其所属group的主任务。
statuscode可能的值为1，2，3，4，分别对应‘Created’，‘Deleted’，‘Hidden’，‘TBD’。要求按如下格式输出：
groupid, creationdate,creator,created,deleted
其中groupid为任务组id,
CreationDate为该任务组里面的主任务的CreationDate
creator为该任务组里面的主任务的creator,
created是一个统计数，统计该任务组里面statuscode为1的任务数量
deleted是一个统计数，统计该任务组里面statuscode为2的任务数量

Answer 1

try
select 
  groupid, 
  creationdate,
  creator,
  created=sum(case when statuscode=1 then 1 else 0 end),
  deleted=sum(case when statuscode=2 then 1 else 0 end)
from
  任务表
group by
  groupid, 
  creationdate,
  creator 
  

Answer 2

SELECT GROUPID, 
CREATIONDATE = MAX(CASE WHEN MAININDICATOR = 1  THEN CREATIONDATE),
CREATOR = MAX(CASE WHEN MAININDICATOR = 1  THEN CREATOR),
CREATED = SUM(CASE WHEN STATUSCODE = 1 THEN 1 END),
DELETED = SUM(CASE WHEN STATUSCODE = 2 THEN 1 END)
FROM 任务表
GROUP BY GROUPID

Answer 3

SELECT GROUPID, 
CREATIONDATE = MAX(CASE WHEN MAININDICATOR = 1  THEN CREATIONDATE END),
CREATOR = MAX(CASE WHEN MAININDICATOR = 1  THEN CREATOR END),
CREATED = SUM(CASE WHEN STATUSCODE = 1 THEN 1 END),
DELETED = SUM(CASE WHEN STATUSCODE = 2 THEN 1 END)
FROM 任务表
GROUP BY GROUPID

Answer 4

测试数据：
TaskID GroupID MainIndicator CreateDate Creator StatusCode
1 1 TRUE 2008-10-12 Jack 1
2 1 FALSE 2008-10-13 Joe 2
3 3 FALSE 2008-10-14 Neo 2
4 2 FALSE 2008-10-15 Varic 1
5 3 FALSE 2008-10-16 James 1
6 2 FALSE 2008-10-17 Barret 2
7 3 FALSE 2008-10-18 Gerald 3
8 2 TRUE 2008-10-19 John 4
9 2 FALSE 2008-10-20 Amy 1
10 2 FALSE 2008-10-21 Allan 2
11 2 FALSE 2008-10-22 Godeis 3
12 2 FALSE 2008-10-23 Athena 1
13 3 FALSE 2008-10-24 Jack 2
14 3 TRUE 2008-10-25 Ass 3
15 2 FALSE 2008-10-26 Jack 4
16 3 FALSE 2008-10-27 Biges 4
17 3 FALSE 2008-10-28 Gosi 2输出：
GroupID CreateDate Creator Created Deleted
1 2008-10-12 Jack 1 1
2 2008-10-19 John 3 2
3 2008-10-25 Ass 1 3

Answer 5

select
   GroupID,CreateDate, Creator ,Created ,Deleted 
from
   tb t
where
   CreateDate=(select min(CreateDate) from tb where Deleted=t.Deleted)

Answer 6

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-02-01 19:36:55
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- 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]([TaskID] int,[GroupID] int,[MainIndicator] varchar(5),[CreateDate] datetime,[Creator] varchar(6),[StatusCode] int)
insert [tb]
select 1,1,'TRUE','2008-10-12','Jack',1 union all
select 2,1,'FALSE','2008-10-13','Joe',2 union all
select 3,3,'FALSE','2008-10-14','Neo',2 union all
select 4,2,'FALSE','2008-10-15','Varic',1 union all
select 5,3,'FALSE','2008-10-16','James',1 union all
select 6,2,'FALSE','2008-10-17','Barret',2 union all
select 7,3,'FALSE','2008-10-18','Gerald',3 union all
select 8,2,'TRUE','2008-10-19','John',4 union all
select 9,2,'FALSE','2008-10-20','Amy',1 union all
select 10,2,'FALSE','2008-10-21','Allan',2 union all
select 11,2,'FALSE','2008-10-22','Godeis',3 union all
select 12,2,'FALSE','2008-10-23','Athena',1 union all
select 13,3,'FALSE','2008-10-24','Jack',2 union all
select 14,3,'TRUE','2008-10-25','Ass',3 union all
select 15,2,'FALSE','2008-10-26','Jack',4 union all
select 16,3,'FALSE','2008-10-27','Biges',4 union all
select 17,3,'FALSE','2008-10-28','Gosi',2
--------------开始查询--------------------------
select 
  groupid, 
  CreateDate,
  creator,
  created=sum(case when statuscode=1 then 1 else 0 end),
  deleted=sum(case when statuscode=2 then 1 else 0 end)
from
  tb
group by
  groupid, 
  CreateDate,
  creator ----------------结果----------------------------
/* groupid     CreateDate              creator created     deleted
----------- ----------------------- ------- ----------- -----------
1           2008-10-12 00:00:00.000 Jack    1           0
1           2008-10-13 00:00:00.000 Joe     0           1
2           2008-10-15 00:00:00.000 Varic   1           0
2           2008-10-17 00:00:00.000 Barret  0           1
2           2008-10-19 00:00:00.000 John    0           0
2           2008-10-20 00:00:00.000 Amy     1           0
2           2008-10-21 00:00:00.000 Allan   0           1
2           2008-10-22 00:00:00.000 Godeis  0           0
2           2008-10-23 00:00:00.000 Athena  1           0
2           2008-10-26 00:00:00.000 Jack    0           0
3           2008-10-14 00:00:00.000 Neo     0           1
3           2008-10-16 00:00:00.000 James   1           0
3           2008-10-18 00:00:00.000 Gerald  0           0
3           2008-10-24 00:00:00.000 Jack    0           1
3           2008-10-25 00:00:00.000 Ass     0           0
3           2008-10-27 00:00:00.000 Biges   0           0
3           2008-10-28 00:00:00.000 Gosi    0           1(17 行受影响)
*/

Answer 7

select 
  a.GroupID,
  convert(varchar(10),
  CreateDate,120) as CreateDate,
  a.Creator,
  b.Created,
  b.Deleted 
from
  tb a
left join
(
  select 
    groupid, 
    created=sum(case when statuscode=1 then 1 else 0 end),
    deleted=sum(case when statuscode=2 then 1 else 0 end)
  from
    tb 
  group by
    groupid
) b
on a.groupid=b.groupid
where a.MainIndicator='TRUE'/**
GroupID     CreateDate Creator Created     Deleted     
----------- ---------- ------- ----------- ----------- 
1           2008-10-12 Jack    1           1
2           2008-10-19 John    3           2
3           2008-10-25 Ass     1           3
**/

Answer 8

select 
  a.GroupID,
  convert(varchar(10),
  CreateDate,120) as CreateDate,
  a.Creator,
  b.Created,
  b.Deleted 
from
  tb a
left join
(
  select 
    groupid, 
    created=sum(case when statuscode=1 then 1 else 0 end),
    deleted=sum(case when statuscode=2 then 1 else 0 end)
  from
    tb 
  group by
    groupid
) b
on a.groupid=b.groupid
where a.MainIndicator='TRUE'引用一下树人大哥的