有一表:
部门 编号 代码 项目
1 001 1001 001
1 001 1001 002
1 002 1001 002
1 003 1002 002
1 003 1002 004
1 003 1005 001
1 003 1006 001
1 003 1006 002
1 003 1006 003
2 101 1001 002
2 101 1002 001
2 101 1003 002
2 103 1006 001
2 107 1006 001
2 107 1006 002
2 108 1001 001
2 108 1002 001
2 108 1003 001
2 108 1004 002
2 108 1005 002
2 108 1006 001得到1:
部门 代码种类<=2的编号数 代码种类<=3并且>=5的编号数 代码种类>5的编号数
1 2 1 0
2 2 1 1得到2:
部门 项目种类<=2的编号数 项目种类<=3并且>=5的编号数 项目种类>5的编号数
1 2 0 1
2 3 0 1
sql语句怎么写?谢谢!
注:若项目相同但代码不同则含义不相同。
部门 编号 代码 项目
1 001 1001 001
1 001 1001 002
1 002 1001 002
1 003 1002 002
1 003 1002 004
1 003 1005 001
1 003 1006 001
1 003 1006 002
1 003 1006 003
2 101 1001 002
2 101 1002 001
2 101 1003 002
2 103 1006 001
2 107 1006 001
2 107 1006 002
2 108 1001 001
2 108 1002 001
2 108 1003 001
2 108 1004 002
2 108 1005 002
2 108 1006 001得到1:
部门 代码种类<=2的编号数 代码种类<=3并且>=5的编号数 代码种类>5的编号数
1 2 1 0
2 2 1 1得到2:
部门 项目种类<=2的编号数 项目种类<=3并且>=5的编号数 项目种类>5的编号数
1 2 0 1
2 3 0 1
sql语句怎么写?谢谢!
注:若项目相同但代码不同则含义不相同。
declare @s table (部门 int,编号 varchar(3),代码 int,项目 varchar(3))
insert into @s
select 1,'001',1001,'001' union all
select 1,'001',1001,'002' union all
select 1,'002',1001,'002' union all
select 1,'003',1002,'002' union all
select 1,'003',1002,'004' union all
select 1,'003',1005,'001' union all
select 1,'003',1006,'001' union all
select 1,'003',1006,'002' union all
select 1,'003',1006,'003' union all
select 2,'101',1001,'002' union all
select 2,'101',1002,'001' union all
select 2,'101',1003,'002' union all
select 2,'103',1006,'001' union all
select 2,'107',1006,'001' union all
select 2,'107',1006,'002' union all
select 2,'108',1001,'001' union all
select 2,'108',1002,'001' union all
select 2,'108',1003,'001' union all
select 2,'108',1004,'002' union all
select 2,'108',1005,'002' union all
select 2,'108',1006,'001'
select 部门,
[代码种类 <=2的编号数]=sum(case when 种类<=2 then 1 else 0 end),
[代码种类 <=3并且>=5的编号数]=sum(case when 种类>=3 and 种类<=5 then 1 else 0 end),
[代码种类>5的编号数]=sum(case when 种类>5 then 1 else 0 end)
from (select 部门,编号,种类=count(distinct 代码) from @s group by 部门,编号)a
group by 部门
--> 测试数据: @s
declare @s table (部门 int,编号 varchar(3),代码 int,项目 varchar(3))
insert into @s
select 1,'001',1001,'001' union all
select 1,'001',1001,'002' union all
select 1,'002',1001,'002' union all
select 1,'003',1002,'002' union all
select 1,'003',1002,'004' union all
select 1,'003',1005,'001' union all
select 1,'003',1006,'001' union all
select 1,'003',1006,'002' union all
select 1,'003',1006,'003' union all
select 2,'101',1001,'002' union all
select 2,'101',1002,'001' union all
select 2,'101',1003,'002' union all
select 2,'103',1006,'001' union all
select 2,'107',1006,'001' union all
select 2,'107',1006,'002' union all
select 2,'108',1001,'001' union all
select 2,'108',1002,'001' union all
select 2,'108',1003,'001' union all
select 2,'108',1004,'002' union all
select 2,'108',1005,'002' union all
select 2,'108',1006,'001'--1
select 部门,
[代码种类 <=2的编号数]=sum(case when 种类<=2 then 1 else 0 end),
[代码种类 <=3并且>=5的编号数]=sum(case when 种类>=3 and 种类<=5 then 1 else 0 end),
[代码种类>5的编号数]=sum(case when 种类>5 then 1 else 0 end)
from (select 部门,编号,种类=count(distinct 代码) from @s group by 部门,编号)a
group by 部门
--2
select 部门,
[项目种类 <=2的编号数]=sum(case when 种类<=2 then 1 else 0 end),
[项目种类 <=3并且>=5的编号数]=sum(case when 种类>=3 and 种类<=5 then 1 else 0 end),
[项目种类>5的编号数]=sum(case when 种类>5 then 1 else 0 end)
from (select 部门,编号,种类=count(distinct 项目) from @s group by 部门,编号)a
group by 部门
---
应该是“项目种类 >=3并且<=5的编号数”吧
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] int,[编号] varchar(3),[代码] int,[项目] varchar(3))
insert [tb]
select 1,'001',1001,'001' union all
select 1,'001',1001,'002' union all
select 1,'002',1001,'002' union all
select 1,'003',1002,'002' union all
select 1,'003',1002,'004' union all
select 1,'003',1005,'001' union all
select 1,'003',1006,'001' union all
select 1,'003',1006,'002' union all
select 1,'003',1006,'003' union all
select 2,'101',1001,'002' union all
select 2,'101',1002,'001' union all
select 2,'101',1003,'002' union all
select 2,'103',1006,'001' union all
select 2,'107',1006,'001' union all
select 2,'107',1006,'002' union all
select 2,'108',1001,'001' union all
select 2,'108',1002,'001' union all
select 2,'108',1003,'001' union all
select 2,'108',1004,'002' union all
select 2,'108',1005,'002' union all
select 2,'108',1006,'001'
---查询---
select 部门,编号,count(distinct 代码) 代码种类,count(distinct 项目) as 项目种类 from tb group by 部门,编号--1.
select
部门,
[代码种类<=2的编号数]=sum(case when 代码种类<=2 then 1 else 0 end),
[代码种类>=3并且<=5的编号数]=sum(case when 代码种类 between 3 and 5 then 1 else 0 end),
[代码种类>5的编号数]=sum(case when 代码种类>5 then 1 else 0 end)
from
(select 部门,编号,count(distinct 代码) 代码种类,count(distinct 项目) as 项目种类 from tb group by 部门,编号) t
group by
部门/**
部门 代码种类<=2的编号数 代码种类 <=3并且>=5的编号数 代码种类>5的编号数
----------- ----------- ----------------- -----------
1 2 1 0
2 2 1 1(所影响的行数为 2 行)
**/--2.
select
部门,
[项目种类<=2的编号数]=sum(case when 项目种类<=2 then 1 else 0 end),
[项目种类 >=3并且<=5的编号数]=sum(case when 项目种类 between 3 and 5 then 1 else 0 end),
[项目种类>5的编号数]=sum(case when 项目种类>5 then 1 else 0 end)
from
(select 部门,编号,count(distinct 代码) 代码种类,count(distinct 项目) as 项目种类 from tb group by 部门,编号) t
group by
部门/**
部门 代码种类<=2的编号数 代码种类 <=3并且>=5的编号数 代码种类>5的编号数
----------- ----------- ----------------- -----------
1 2 1 0
2 4 0 0(所影响的行数为 2 行)
**/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-09 10:14:28
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] int,[编号] varchar(3),[代码] int,[项目] varchar(3))
insert [tb]
select 1,'001',1001,'001' union all
select 1,'001',1001,'002' union all
select 1,'002',1001,'002' union all
select 1,'003',1002,'002' union all
select 1,'003',1002,'004' union all
select 1,'003',1005,'001' union all
select 1,'003',1006,'001' union all
select 1,'003',1006,'002' union all
select 1,'003',1006,'003' union all
select 2,'101',1001,'002' union all
select 2,'101',1002,'001' union all
select 2,'101',1003,'002' union all
select 2,'103',1006,'001' union all
select 2,'107',1006,'001' union all
select 2,'107',1006,'002' union all
select 2,'108',1001,'001' union all
select 2,'108',1002,'001' union all
select 2,'108',1003,'001' union all
select 2,'108',1004,'002' union all
select 2,'108',1005,'002' union all
select 2,'108',1006,'001'
--------------开始查询--------------------------
--1
select
部门,
[代码种类 <=2的编号数]=sum(case when 种类<=2 then 1 else 0 end),
[代码种类 <=3并且>=5的编号数]=sum(case when 种类>=3 and 种类<=5 then 1 else 0 end),
[代码种类>5的编号数]=sum(case when 种类>5 then 1 else 0 end)
from
(select 部门,编号,种类=count(distinct 代码) from tb group by 部门,编号)a
group by
部门
--2
select
部门,
[项目种类 <=2的编号数]=sum(case when 种类<=2 then 1 else 0 end),
[项目种类 <=3并且>=5的编号数]=sum(case when 种类>=3 and 种类<=5 then 1 else 0 end),
[项目种类>5的编号数]=sum(case when 种类>5 then 1 else 0 end)
from
(select 部门,编号,种类=count(distinct 项目) from tb group by 部门,编号)a
group by
部门----------------结果----------------------------
/* 部门 代码种类 <=2的编号数 代码种类 <=3并且>=5的编号数 代码种类>5的编号数
----------- ------------ ----------------- -----------
1 2 1 0
2 2 1 1(2 行受影响)部门 项目种类 <=2的编号数 项目种类 <=3并且>=5的编号数 项目种类>5的编号数
----------- ------------ ----------------- -----------
1 2 1 0
2 4 0 0(2 行受影响)*/
---
应该是“项目种类 >=3并且 <=5的编号数”吧pt1314917
你说得对
谢谢你们!!!
但第2种统计都有误。
部门1:
编号为001有1个代码2个项目
编号为002有1个代码1个项目
编号为003有3个代码6个项目
部门2:
编号为101有3个代码3个项目
编号为103有1个代码1个项目
编号为007有1个代码2个项目
编号为008有6个代码6个项目
所以结果应该是:
部门 项目种类<=2的编号数 项目种类>=3并且<=5的编号数 项目种类>5的编号数
1 2 0 1
2 3 0 1
部门 项目种类 <=2的编号数 项目种类>=3并且 <=5的编号数 项目种类>5的编号数
1 2 0 1
2 2 1 1
insert into @tb
select 1,'001',1001,'001' union all
select 1,'001',1001,'002' union all
select 1,'002',1001,'002' union all
select 1,'003',1002,'002' union all
select 1,'003',1002,'004' union all
select 1,'003',1005,'001' union all
select 1,'003',1006,'001' union all
select 1,'003',1006,'002' union all
select 1,'003',1006,'003' union all
select 2,'101',1001,'002' union all
select 2,'101',1002,'001' union all
select 2,'101',1003,'002' union all
select 2,'103',1006,'001' union all
select 2,'107',1006,'001' union all
select 2,'107',1006,'002' union all
select 2,'108',1001,'001' union all
select 2,'108',1002,'001' union all
select 2,'108',1003,'001' union all
select 2,'108',1004,'002' union all
select 2,'108',1005,'002' union all
select 2,'108',1006,'001'
----1
;with china as
(
select 部门,编号,count(distinct(代码)) 次数 from @tb
group by 部门,编号
)
select 部门,[代码种类 <=2的编号数]=sum(case when 次数<2 then 1 else 0 end),
[代码种类 <=3并且>=5的编号数]=sum(case when 次数 between 2 and 5 then 1 else 0 end),
[代码种类>5的编号数]=sum(case when 次数>5 then 1 else 0 end)
from china
group by 部门
--
;with china1 as
(
select 部门,编号,COUNT(distinct(项目)) 次数 from @tb
group by 部门,编号
)
select 部门,[代码种类 <=2的编号数]=sum(case when 次数<2 then 1 else 0 end),
[代码种类 <=3并且>=5的编号数]=sum(case when 次数 between 2 and 5 then 1 else 0 end),
[代码种类>5的编号数]=sum(case when 次数>5 then 1 else 0 end)
from china1
group by 部门