求段统计SQL

cppfaq 2010-02-01 06:31:40
任务表：
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的任务数量



...全文
116 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
幸运的意外 2010-02-02
  • 打赏
  • 举报
 回复
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'

引用一下树人大哥的
百年树人 2010-02-01
  • 打赏
  • 举报
 回复 
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

**/
--小F-- 2010-02-01
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------

-- 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 行受影响)

*/
--小F-- 2010-02-01
  • 打赏
  • 举报
 回复 
select

   GroupID,CreateDate, Creator ,Created ,Deleted 

from

   tb t

where

   CreateDate=(select min(CreateDate) from tb where Deleted=t.Deleted)
丰云 2010-02-01
  • 打赏
  • 举报
 回复
qf
cppfaq 2010-02-01
  • 打赏
  • 举报
 回复
测试数据：
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
misterliwei 2010-02-01
  • 打赏
  • 举报
 回复 


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
misterliwei 2010-02-01
  • 打赏
  • 举报
 回复 
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
百年树人 2010-02-01
  • 打赏
  • 举报
 回复
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
ws_hgo 2010-02-01
  • 打赏
  • 举报
 回复
貌似说复杂啦

直接上数据
和你想要的结构把
【SQL统计】简单实用的统计SQL
SQL统计实现按年按月分组统计统计单一统计分组统计分组排序统计关联查询和分组排序统计统计每月的数据多分组条件统计SQL模板结尾语 统计 正常在工作中经常会遇到需要统计的需求，所以使用sql完成数据统计成了必不可...
SQL语句，根据时间段统计
SQL语句根据时间段统计，比如七天的数据划分这24小时，00:00-01:00,01:00-02:00,02:00-03:00,03:00-04:00…23:00-00:00。 先看sql语句 SELECT strftime('%H:00', field_name) AS time, count(field_name) AS num ...
sql 按时间段统计数据个数
sql 按时间段统计数据个数
sql统计各分数段人数示例
SQL语句可以很方便帮助我们进行统计分析，例如统计各分数段人数。90-100分数段内的标记为A，80-89分数段内的标记为B，70-79分数段内的标记为C，60-69分数段内的标记为D，小于60的标记为E，并统计各分数段的人数。 –...
sql 统计各班不同分数段的人数（案例v1）
sql语句 分组统计各分数段人数
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章