再问一个分类统计的
表结构：
Ｂ Ｎ ＮＵ
字段值：
＿＿＿＿＿＿＿
Ｂ1 Ｎ1 １
Ｂ1 Ｎ1 ２
Ｂ1 Ｎ2 ３
Ｂ1 Ｎ2 ４
Ｂ1 Ｎ3 5
Ｂ1 Ｎ4 6
Ｂ2 Ｎ1 ２
Ｂ2 Ｎ1 ３
Ｂ2 Ｎ２ ２
Ｂ2 Ｎ２ ３
Ｂ2 Ｎ３ ２
Ｂ2 Ｎ４ ３
Ｂ2 Ｎ４ 4
． ． ．
． ． ．
． ． ．
想输出：
Ｂ1 Ｎ1 3
Ｎ2 7
Ｎ3 5
N4 6
B2 Ｎ1 5
Ｎ２ 5
Ｎ３ ２
Ｎ４ 7
想输出：
[解决办法]
--创建测试环境
create table t(B varchar(10),N varchar(10),NU int)
--插入测试数据
insert t(B,N,NU)
select 'B1 ', 'N1 ', '1 ' union all
select 'B1 ', 'N1 ', '2 ' union all
select 'B1 ', 'N2 ', '3 ' union all
select 'B1 ', 'N2 ', '4 ' union all
select 'B1 ', 'N3 ', '5 ' union all
select 'B1 ', 'N4 ', '6 ' union all
select 'B2 ', 'N1 ', '2 ' union all
select 'B2 ', 'N1 ', '3 ' union all
select 'B2 ', 'N2 ', '2 ' union all
select 'B2 ', 'N2 ', '3 ' union all
select 'B2 ', 'N3 ', '2 ' union all
select 'B2 ', 'N4 ', '3 ' union all
select 'B2 ', 'N4 ', '4 '
--求解过程
select B,N,sum(NU) as NU from t
group by B,N
order by B,N
--删除测试环境
drop table t
/*--测试结果
*/
B N NU
---------- ---------- -----------
B1 N1 3
B1 N2 7
B1 N3 5
B1 N4 6
B2 N1 5
B2 N2 5
B2 N3 2
B2 N4 7
（所影响的行数为 8 行）
/*
[解决办法]
create table t (B varchar(5), N varchar(5), NU int)
insert into t
select 'B1 ', 'N1 ', '1 '
union select 'B1 ', 'N1 ', '2 '
union select 'B1 ', 'N2 ', '3 '
union select 'B1 ', 'N2 ', '4 '
union select 'B1 ', 'N3 ', '5 '
union select 'B1 ', 'N4 ', '6 '
union select 'B2 ', 'N1 ', '2 '
union select 'B2 ', 'N1 ', '3 '
union select 'B2 ', 'N2 ', '2 '
union select 'B2 ', 'N2 ', '3 '
union select 'B2 ', 'N3 ', '2 '
union select 'B2 ', 'N4 ', '3 '
union select 'B2 ', 'N4 ', '4 '
SELECT B, N, SUM(NU) FROM T GROUP BY B, N
ORDER BY B, N
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(B varchar(10),N varchar(10),BU int)
insert into tb(B,N,BU) values( 'B1 ', 'N1 ',1)
insert into tb(B,N,BU) values( 'B1 ', 'N1 ',2)
insert into tb(B,N,BU) values( 'B1 ', 'N2 ',3)
insert into tb(B,N,BU) values( 'B1 ', 'N2 ',4)
insert into tb(B,N,BU) values( 'B1 ', 'N3 ',5)
insert into tb(B,N,BU) values( 'B1 ', 'N4 ',6)
insert into tb(B,N,BU) values( 'B2 ', 'N1 ',2)
insert into tb(B,N,BU) values( 'B2 ', 'N1 ',3)
insert into tb(B,N,BU) values( 'B2 ', 'N2 ',2)
insert into tb(B,N,BU) values( 'B2 ', 'N2 ',3)
insert into tb(B,N,BU) values( 'B2 ', 'N3 ',2)
insert into tb(B,N,BU) values( 'B2 ', 'N4 ',3)
insert into tb(B,N,BU) values( 'B2 ', 'N4 ',4)
select B,N,BU from
(
select N,case when N=(select min(N) from
(
select B,N,sum(BU) BU from tb group by B,N
) t
where B=a.B) then B else ' ' end as B,BU
from
(
select B,N,sum(BU) BU from tb group by B,N
) a
) m
drop table tb
/*
B N BU
---------- ---------- -----------
B1 N1 3
N2 7
N3 5
N4 6
B2 N1 5
N2 5
N3 2
N4 7
（所影响的行数为 8 行）
*/
