select a=case when b is null then cast(aa as varchar) else b end,c from( select aa=a,b,c from 表a union all select a,null,c=sum(c) from 表a group by a ) a order by aa,b
--字段名错了,改一下: select 类别=case when b is null then cast(a as varchar) else b end,数量=c from( select a,b,c from @表a union all select a,null,c=sum(c) from @表a group by a ) a order by a,b
--下面是测试--测试数据 declare @表a table(a int,b varchar(10),c int) insert into @表a select 1,'01',3 union all select 1,'02',4 union all select 1,'03',5 union all select 2,'04',7 union all select 2,'05',7 union all select 3,'06',8 union all select 3,'07',10--查询 select 类别=case when b is null then cast(a as varchar) else b end,数量=c from( select a,b,c from @表a union all select a,null,c=sum(c) from @表a group by a ) a order by a,b/*--测试结果 类别 数量 ------------------------------ ----------- 1 12 01 3 02 4 03 5 2 14 04 7 05 7 3 18 06 8 07 10(所影响的行数为 10 行) --*/
--下面是测试--测试数据 declare @表a table(a int,b varchar(10),c int) insert into @表a select 1,'01',3 union all select 1,'02',4 union all select 1,'03',5 union all select 2,'04',7 union all select 2,'05',7 union all select 3,'06',8 union all select 3,'07',10--查询 select 类别=case when b is null then cast(a as varchar) else space(4)+b end,数量=c from( select a,b,c from @表a union all select a,null,c=sum(c) from @表a group by a ) a order by a,b/*--测试结果类别 数量 ------------------------------ ----------- 1 12 01 3 02 4 03 5 2 14 04 7 05 7 3 18 06 8 07 10(所影响的行数为 10 行) --*/
select 类别=case when b is null then cast(a as varchar) else b end,数量=c
from(
select a,b,c from @表a
union all
select a,null,c=sum(c) from @表a group by a
) a order by a,b
declare @表a table(a int,b varchar(10),c int)
insert into @表a
select 1,'01',3
union all select 1,'02',4
union all select 1,'03',5
union all select 2,'04',7
union all select 2,'05',7
union all select 3,'06',8
union all select 3,'07',10--查询
select 类别=case when b is null then cast(a as varchar) else b end,数量=c
from(
select a,b,c from @表a
union all
select a,null,c=sum(c) from @表a group by a
) a order by a,b/*--测试结果
类别 数量
------------------------------ -----------
1 12
01 3
02 4
03 5
2 14
04 7
05 7
3 18
06 8
07 10(所影响的行数为 10 行)
--*/
--下面是测试--测试数据
declare @表a table(a int,b varchar(10),c int)
insert into @表a
select 1,'01',3
union all select 1,'02',4
union all select 1,'03',5
union all select 2,'04',7
union all select 2,'05',7
union all select 3,'06',8
union all select 3,'07',10--查询
select 类别=case when b is null then cast(a as varchar) else space(4)+b end,数量=c
from(
select a,b,c from @表a
union all
select a,null,c=sum(c) from @表a group by a
) a order by a,b/*--测试结果类别 数量
------------------------------ -----------
1 12
01 3
02 4
03 5
2 14
04 7
05 7
3 18
06 8
07 10(所影响的行数为 10 行)
--*/