原表:title module menu1 menu2 status
t1 1 3 4 n
t2 1 3 4 n
t3 1 3 4 d
t4 1 3 4 y
t5 1 3 5 n
t6 1 3 5 y
t7 1 3 5 y
t8 2 3 7 n
t9 2 3 6 y
t10 3 4 9 y ======
想得到如下结果: module menu1 menu2 Count_statusN Count_statusY Count_statusD Count
1 3 4 2 1 1 4
1 3 5 1 2 0 3
2 3 6 0 1 0 1
2 3 7 1 0 0 1
3 4 9 0 1 0 1
t1 1 3 4 n
t2 1 3 4 n
t3 1 3 4 d
t4 1 3 4 y
t5 1 3 5 n
t6 1 3 5 y
t7 1 3 5 y
t8 2 3 7 n
t9 2 3 6 y
t10 3 4 9 y ======
想得到如下结果: module menu1 menu2 Count_statusN Count_statusY Count_statusD Count
1 3 4 2 1 1 4
1 3 5 1 2 0 3
2 3 6 0 1 0 1
2 3 7 1 0 0 1
3 4 9 0 1 0 1
(
title varchar(100),
module int,
menu1 int,
menu2 int,
[status] char(1)
)
insert #
select 't1', '1', '3', '4', 'n' union all
select 't2', '1', '3', '4', 'n' union all
select 't3', '1', '3', '4', 'd' union all
select 't4', '1', '3', '4', 'y' union all
select 't5', '1', '3', '5', 'n' union all
select 't6', '1', '3', '5', 'y' union all
select 't7', '1', '3', '5', 'y' union all
select 't8', '2', '3', '7', 'n' union all
select 't9', '2', '3', '6', 'y' union all
select 't10', '3', '4', '9', 'y'--SQL
select
module, menu1, menu2,
Count_statusN = ISNULL(n, 0), Count_statusY = ISNULL(y, 0), Count_statusD = ISNULL(d, 0),
[COUNT] = ISNULL(n, 0) + ISNULL(y, 0) + ISNULL(d, 0)
from
(SELECT module, menu1, menu2, [status], cnt = COUNT(*) FROM # group by module, menu1, menu2, [status]) a
pivot
(sum(cnt) for [status] in ([n],[y],[d])) b
--RESULT
/*
module menu1 menu2 Count_statusN Count_statusY Count_statusD COUNT
1 3 4 2 1 1 4
1 3 5 1 2 0 3
2 3 6 0 1 0 1
2 3 7 1 0 0 1
3 4 9 0 1 0 1
*/
select module,menu1,menu2,sum(case status when 'N' then 1 else 0 end) Count_statusN,
sum(case status when 'Y' then 1 else 0 end) Count_statusY,
sum(case status when 'D' then 1 else 0 end) Count_statusD,
count(status) [COUNT]
from tb
group by module,menu1,menu2
CREATE TABLE tb
(
title varchar(100),
module int,
menu1 int,
menu2 int,
[status] char(1)
)
insert tb
select 't1', '1', '3', '4', 'n' union all
select 't2', '1', '3', '4', 'n' union all
select 't3', '1', '3', '4', 'd' union all
select 't4', '1', '3', '4', 'y' union all
select 't5', '1', '3', '5', 'n' union all
select 't6', '1', '3', '5', 'y' union all
select 't7', '1', '3', '5', 'y' union all
select 't8', '2', '3', '7', 'n' union all
select 't9', '2', '3', '6', 'y' union all
select 't10', '3', '4', '9', 'y'
goselect module,menu1,menu2,sum(case status when 'N' then 1 else 0 end) Count_statusN,
sum(case status when 'Y' then 1 else 0 end) Count_statusY,
sum(case status when 'D' then 1 else 0 end) Count_statusD,
count(status) [COUNT]
from tb
group by module,menu1,menu2drop table tbmodule menu1 menu2 Count_statusN Count_statusY Count_statusD COUNT
----------- ----------- ----------- ------------- ------------- ------------- -----------
1 3 4 2 1 1 4
1 3 5 1 2 0 3
2 3 6 0 1 0 1
2 3 7 1 0 0 1
3 4 9 0 1 0 1(5 行受影响)