大家好,这个问题望各位高手帮我解决一下,谢谢!
这是数据库的表:(名称: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
(储运部总是在第一个?请各位帮忙,谢谢!!!!)
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 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)
您的回答给我解决大部分问题,(合计)这列怎么到第二行了?
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
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)
ok,解决问题,谢谢!