select isnull(f1,'') as f1,f2,f3
from(
Select f1,f2,f3 from t1
union all
select '总计' as f2,
null as f2,sum(f3) as f3 from t1
)t
order by case when f1 is null then 1 else 0 end
from(
Select f1,f2,f3 from t1
union all
select '总计' as f2,
null as f2,sum(f3) as f3 from t1
)t
order by case when f1 is null then 1 else 0 end
union all
select '' as f1,'总计' as f2,
sum(f3) as f3 from t1
Order by (case when
cast(f1 as varchar(8)='' then 1 else 0),f1
from(
Select f1,f2,f3 from t1
union all
select '总计' as f2,
null as f2,sum(f3) as f3 from t1
)t
order by case when f1 is null then 1 else 0 end
f1=isnull(f1,''),f2=isnull(f2,'总计'),f3=sum(f3)
from
t1
group by
f1,f2
with rollup
having
grouping(f1)=1 or grouping(f2)=0呵呵!刚学到的!:)
Select convert(varchar,f1) f1,f2,f3 from t1
union all
select '','总计' as f1, sum(f3) as f3 from t1
order by f1 desc
create table t1(f1 int, f2 varchar(10), f3 int)
go
insert t1 select 10, 'A1', 10 union all select 3, 'A2', 10
union all select 8 , 'A3', 10 union all select 9, 'A9', 20
--查询
select f1,f2,f3
from(
Select f1,f2,f3 from t1
union all
select null,'总计',sum(f3) from t1
)t
order by (case when f1 is null then 1 else 0 end), f1
--清除
drop table t1
union all
select '','总计' as f2, sum(f3) as f3 from t1
from(
Select f1,f2,f3 from t1
union all
select '总计' as f2,
null as f2,sum(f3) as f3 from t1
)t
order by case when f1 is null then 1 else 0 end