分类A包含的子分类(1,2,3)
分类B包含的子分类(4,5,6)
分类C包含的子分类(7,8,9)现有数据
id 用户 所属分类
1 aa 1
2 aa 1
3 aa 2
4 aa 3
5 bb 4
6 bb 5
7 bb 6
8 cc 7
9 cc 8----------
期望的结果
用户 分类 统计
aa A 4
bb B 3
cc C 2
分类B包含的子分类(4,5,6)
分类C包含的子分类(7,8,9)现有数据
id 用户 所属分类
1 aa 1
2 aa 1
3 aa 2
4 aa 3
5 bb 4
6 bb 5
7 bb 6
8 cc 7
9 cc 8----------
期望的结果
用户 分类 统计
aa A 4
bb B 3
cc C 2
A 1
A 2
A 3
B 4
B 5
B 6
C 7
C 8
C 9
用户,
case when 所属分类 IN(1,2,3) then 'A' when 所属分类 IN(4,5,6) then 'B' when 所属分类 IN(7,8,9) then 'C' end,
count(1)
FROM
TB
GROUP BY
用户,case when 所属分类 IN(1,2,3) then 'A' when 所属分类 IN(4,5,6) then 'B' when 所属分类 IN(7,8,9) then 'C' end
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-23 15:57:54
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[用户] varchar(2),[所属分类] int)
insert [tb]
select 1,'aa',1 union all
select 2,'aa',1 union all
select 3,'aa',2 union all
select 4,'aa',3 union all
select 5,'bb',4 union all
select 6,'bb',5 union all
select 7,'bb',6 union all
select 8,'cc',7 union all
select 9,'cc',8
--------------开始查询--------------------------
SELECT
用户,
case when 所属分类 IN(1,2,3) then 'A' when 所属分类 IN(4,5,6) then 'B' when 所属分类 IN(7,8,9) then 'C' end,
count(1)
FROM
TB
GROUP BY
用户,case when 所属分类 IN(1,2,3) then 'A' when 所属分类 IN(4,5,6) then 'B' when 所属分类 IN(7,8,9) then 'C' end
----------------结果----------------------------
/* 用户
---- ---- -----------
aa A 4
bb B 3
cc C 2(3 行受影响)
*/
if object_id('tb') is not null
drop table tb
go
create table tb
(
id int identity(1,1),
用户 varchar(2),
所属分类 int
)
go
insert into tb (用户,所属分类)
select 'aa',1 union all
select 'aa',1 union all
select 'aa',2 union all
select 'aa',3 union all
select 'bb',4 union all
select 'bb',5 union all
select 'bb',6 union all
select 'cc',7 union all
select 'cc',8
go
select 用户,分类,统计=count(*) from
(
select 用户,分类=case when 所属分类 in (1,2,3) then 'A'
when 所属分类 in (4,5,6) then 'B'
else 'C'
end,
所属分类
from tb
) t group by 用户,分类
/*
用户 分类 统计
---- ---- -----------
aa A 4
bb B 3
cc C 2(3 行受影响)
*/
(小F)
drop table flA
go
create table flA (lf int)
insert into flA values(1)
insert into flA values(2)
insert into flA values(3)if object_id('flB') is not null
drop table flB
go
create table flB (lf int)
insert into flB values(4)
insert into flB values(5)
insert into flB values(6)if object_id('flC') is not null
drop table flC
go
create table flC (lf int)
insert into flC values(7)
insert into flC values(8)
insert into flC values(9)if object_id('tb') is not null
drop table tb
gocreate table tb(id int,用户 varchar(50),所属分类 varchar(50))insert into tb values(1,'aa',1)
insert into tb values(2,'aa',1)
insert into tb values(3,'aa',2)
insert into tb values(4,'aa',3)
insert into tb values(5,'bb',4)
insert into tb values(6,'bb',5)
insert into tb values(7,'bb',6)
insert into tb values(8,'cc',7)
insert into tb values(9,'cc',8)
select 用户,分类,
统计=count(*)
from
(
select 用户,case when 所属分类 IN(select * from flA) then 'A'
when 所属分类 IN(select * from flB) then 'B'
when 所属分类 IN(select * from flC) then 'C' ELSE '' END AS '分类' from tb
)t
GROUP BY 用户 ,分类用户 分类 统计
-------------------------------------------------- ---- -----------
aa A 4
bb B 3
cc C 2(3 行受影响)