select case when type=1 then '桔子' when type=2 then '苹果' else '其他' end as type, sum(qty) as qty from tablename group by case when type=1 then '桔子' when type=2 then '苹果' else '其他' end union all select '总共' as type,sum(qty) as qty from tablename
SELECT ISNULL(b.TypeName,其它) type,a.qty FROM 表A a LEFT JOIN ( SELECT 1 Type,'桔子' TypeName UNION SELECT 2 Type,'苹果' TypeName ) b ON a.Type=b.Type随手写未经测试。
上面手误,应该是ISNULL(b.TypeName,'其它')
declare @t table(type int,qty int) insert into @t select 1,2 union all select 2,3 union all select 3,4 union all select 4,3 union all select 5,8select type=isnull(case when type is null then '合计' when type=1 then '桔子' when type=2 then '苹果' else '其他' end,'合计') , sum(qty) from @t group by case when type is null then '合计' when type=1 then '桔子' when type=2 then '苹果' else '其他' end with rollup/* type ---- ----------- 桔子 2 苹果 3 其他 15 合计 20(4 行受影响) */
declare @a table ( type int, qty int ) insert into @a select 1,2 union all select 2,3 union all select 3,4 union all select 4,3 union all select 5,8select case when grouping(type)=1 then '总共' else type end as Type, sum(qty) as Qty from( select (case type when 1 then '桔子' when 2 then '苹果' else '其他' end) as Type, sum(qty) as qty from @a group by case type when 1 then '桔子' when 2 then '苹果' else '其他' end )a group by type with rollup/* Type Qty ---- ----------- 桔子 2 苹果 3 其他 15 总共 20 */
没看清楼主还要统计数量。SELECT ISNULL(b.TypeName,其它) type,SUM(a.qty) qty FROM 表A a LEFT JOIN ( SELECT 1 Type,'桔子' TypeName UNION SELECT 2 Type,'苹果' TypeName ) b ON a.Type=b.Type GROUP BY b.TypeName UNION ALL SELECT '总共',SUM(qty) FROM 表A
这里的 其它 又没加引号,晕 上面手误,应该是ISNULL(b.TypeName,'其它')
DECLARE @test TABLE (type int,qty int)INSERT @test SELECT 1,2 UNION ALL SELECT 2,3 UNION ALL SELECT 3,4 UNION ALL SELECT 4,3 UNION ALL SELECT 5,8SELECT type=CASE type WHEN 1 THEN '桔子' WHEN 2 THEN '苹果' ELSE '其他' END, SUM(ISNULL(qty,0)) FROM @test WHERE type IN(1,2) GROUP BY type UNION ALL SELECT type='其他',SUM(ISNULL(qty,0)) FROM @test WHERE type NOT IN(1,2) UNION ALL SELECT type='总共',SUM(ISNULL(qty,0)) FROM @test
sum(qty) as qty
from tablename
group by case when type=1 then '桔子' when type=2 then '苹果' else '其他' end
union all
select '总共' as type,sum(qty) as qty
from tablename
FROM 表A a
LEFT JOIN
(
SELECT 1 Type,'桔子' TypeName
UNION
SELECT 2 Type,'苹果' TypeName
) b
ON a.Type=b.Type随手写未经测试。
insert into @t select 1,2
union all select 2,3
union all select 3,4
union all select 4,3
union all select 5,8select type=isnull(case when type is null then '合计'
when type=1 then '桔子'
when type=2 then '苹果'
else '其他' end,'合计') ,
sum(qty)
from @t
group by case when type is null then '合计'
when type=1 then '桔子'
when type=2 then '苹果'
else '其他' end with rollup/*
type
---- -----------
桔子 2
苹果 3
其他 15
合计 20(4 行受影响)
*/
(
type int,
qty int
)
insert into @a
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,3 union all
select 5,8select case when grouping(type)=1 then '总共'
else type end as Type,
sum(qty) as Qty
from(
select
(case type
when 1 then '桔子'
when 2 then '苹果'
else '其他' end) as Type,
sum(qty) as qty
from @a
group by
case type
when 1 then '桔子'
when 2 then '苹果'
else '其他' end
)a
group by type with rollup/*
Type Qty
---- -----------
桔子 2
苹果 3
其他 15
总共 20
*/
FROM 表A a
LEFT JOIN
(
SELECT 1 Type,'桔子' TypeName
UNION
SELECT 2 Type,'苹果' TypeName
) b
ON a.Type=b.Type
GROUP BY b.TypeName
UNION ALL SELECT '总共',SUM(qty) FROM 表A
上面手误,应该是ISNULL(b.TypeName,'其它')
UNION ALL SELECT 2,3
UNION ALL SELECT 3,4
UNION ALL SELECT 4,3
UNION ALL SELECT 5,8SELECT type=CASE type WHEN 1 THEN '桔子'
WHEN 2 THEN '苹果'
ELSE '其他' END,
SUM(ISNULL(qty,0))
FROM @test WHERE type IN(1,2) GROUP BY type
UNION ALL
SELECT type='其他',SUM(ISNULL(qty,0))
FROM @test WHERE type NOT IN(1,2)
UNION ALL
SELECT type='总共',SUM(ISNULL(qty,0))
FROM @test