sql 2005 group by with rollup 排序问题? 急切盼回复。

[quchao]

大家好，这个问题望各位高手帮我解决一下，谢谢！
这是数据库的表：(名称:datatable)

user one two three four five six xiaozu bumen
110 a 1.00 1.00 1.00 1.00 1.00 1.00 1-1 一部
110 z 1.00 1.00 1.00 1.00 1.00 1.00 1-1 一部
111 b 1.00 1.00 1.00 1.00 1.00 1.00 2-1 二部
111 y 1.00 1.00 1.00 1.00 1.00 1.00 2-1 二部
112 c 1.00 1.00 1.00 1.00 1.00 1.00 3-1 三部
113 d 1.00 1.00 1.00 1.00 1.00 0.00 4-1 四部
114 e 1.00 1.00 0.00 0.00 0.00 0.00 5-1 五部
115 f 1.00 1.00 0.00 0.00 0.00 0.00 6-1 六部
116 h 1.00 1.00 0.00 0.00 0.00 0.00 6-1 储运部
116 x 1.00 1.00 0.00 0.00 0.00 0.00 6-1 储运部


我想实现这样的效果：

one(小计） two (小计）.......... six((小计）
一部 2 .....
二部 2 ......
三部 1
四部 1
五部 1
六部 1
储运部 2
合计 10

我用了这样方法：（可是部门排序不对，请各位帮忙指教，谢谢！）
SELECT bumen,
SUM(one) AS 招待费, SUM(two) AS 接发车费, SUM(three) AS 差旅费, SUM(four) AS [话 费], SUM(five) AS [工 资],
SUM(six) AS 办公费 from datatable GROUP BY [bumen] with rollup order by bumen

（储运部总是在第一个？请各位帮忙，谢谢！！！！）

[昵称被占用了]

SELECT   bumen,  
SUM(one) AS 招待费, SUM(two) AS 接发车费, SUM(three) AS 差旅费, SUM(four) AS [话 费], SUM(five) AS [工 资], 
 SUM(six) AS 办公费 from datatable GROUP BY [bumen] with rollup order by min(xiaozu)  

[quchao]

@haiwer 您好:
解决大部分问题，可是（合计)又到中间了
SELECT CASE WHEN (grouping(bumen) = 1) THEN '合计' ELSE ISNULL(bumen , 'UNKNOWN') END AS bumen,
SUM(one) AS 招待费, SUM(two) AS 接发车费, SUM(three) AS 差旅费, SUM(four) AS [话 费], SUM(five) AS [工 资],
SUM(six) AS 办公费 from datatable GROUP BY [bumen] with rollup order by min(xiaozu)

[quchao]

@haiwer 您好:
您的回答给我解决大部分问题，（合计）这列怎么到第二行了？

[昵称被占用了]

Try:

SELECT   bumen,  
SUM(one) AS 招待费, SUM(two) AS 接发车费, SUM(three) AS 差旅费, SUM(four) AS [话 费], SUM(five) AS [工 资], 
 SUM(six) AS 办公费 from datatable GROUP BY [bumen] with rollup order by max(xiaozu), CASE WHEN (GROUPING(bumen) = 1) THEN 1 else 0 end
  

[昵称被占用了]

也可以试下,如果不行用4楼语句:

SELECT   bumen,  
SUM(one) AS 招待费, SUM(two) AS 接发车费, SUM(three) AS 差旅费, SUM(four) AS [话 费], SUM(five) AS [工 资], 
 SUM(six) AS 办公费 from datatable GROUP BY [bumen] with rollup order by max(xiaozu)

[quchao]

@haiwer 您好:
ok,解决问题,谢谢!

[高手都到碗里来]

我居然看不到#1，不知道怎么了