进行分组排列之后怎么将每一组的合计union 到组记录的后面?
select a,b,c,d,e
into #buf1
from tb1
group by a
select a,max('合计') as b,sum(c) as c,sum(d) as d,sum(e) as e
into #buf2
from #buf1
select *
from #buf1
union
select * from #buf2
这样会得出下面的排列结果:
-------------------------------------
a b c d e
-------------------------------------
A F1 1 1 1
A F2 2 2 2
A 合计 6 6 6
A F3 3 3 3B F1 1 1 1
B 合计 8 8 8
B F2 2 2 2
B F5 5 5 5
-------------------------------------
请问各位高手,怎么才能让“合计”的那一行处于每一组的最后?
select a,b,c,d,e
into #buf1
from tb1
group by a
select a,max('合计') as b,sum(c) as c,sum(d) as d,sum(e) as e
into #buf2
from #buf1
select *
from #buf1
union
select * from #buf2
这样会得出下面的排列结果:
-------------------------------------
a b c d e
-------------------------------------
A F1 1 1 1
A F2 2 2 2
A 合计 6 6 6
A F3 3 3 3B F1 1 1 1
B 合计 8 8 8
B F2 2 2 2
B F5 5 5 5
-------------------------------------
请问各位高手,怎么才能让“合计”的那一行处于每一组的最后?
select *
from #buf1
union
select * from #buf2
) k
order by a,case when b='合计' then 1 else 0 end,b
(select *
from #buf1
union
select * from #buf2) a order by a,b
from #buf1 order by b
union
select * from #buf2 order by b
into #buf1
from tb1
group by a
select a,max('合计') as b,sum(c) as c,sum(d) as d,sum(e) as e,2 as f
into #buf2
from #buf1
select *
from #buf1
union
select * from #buf2
order by f
或許可以用
with rollup
into #buf2
from #buf1select * from
(select a,b,c,d,e
into #buf1
from tb1
group by a
union all
select * from #buf2
)t
order by t.a,t.b
(
select *
from #buf1
union
select * from #buf2
)AS T ORDER BY A,B
into #buf2
from #buf1select * from
(select a,b,c,d,e
into #buf1
from tb1
group by a
union all
select * from #buf2
)t
order by t.a,case when t.b='合计' then 1 else 0 end,t.b
--------------------另外:
feixianxxx的这段是什么意思?
case when b='合计' then 1 else 0 end