• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

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

quchao 2008-03-06 02:38:28

大家好,这个问题望各位高手帮我解决一下,谢谢!
这是数据库的表:(名称: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

(储运部总是在第一个?请各位帮忙,谢谢!!!!)





...全文
399 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
我居然看不到#1,不知道怎么了
回复
quchao 2008-03-06
@haiwer 您好:
ok,解决问题,谢谢!
回复
也可以试下,如果不行用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)
回复
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

回复
quchao 2008-03-06
@haiwer 您好:
您的回答给我解决大部分问题,(合计)这列怎么到第二行了?
回复
quchao 2008-03-06
@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)

回复
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)

回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-06 02:38
社区公告
暂无公告