现有一表:intdb
记录:
编号,代码,数量,
1, 3, 5
1, 4, 6
2, 5, 8
1, 2, 4,
3, 6, 12
2, 1, 18
4, 5, 12
我想用分组查询得观察家下面果
编号,代码,数量,
1, 3, 5
1, 4, 6
1, 2, 4
总计: 15
2, 5, 8
2, 1, 18
总计: 26
3, 6, 12
总计: 12
4, 5, 12
总计: 12
记录:
编号,代码,数量,
1, 3, 5
1, 4, 6
2, 5, 8
1, 2, 4,
3, 6, 12
2, 1, 18
4, 5, 12
我想用分组查询得观察家下面果
编号,代码,数量,
1, 3, 5
1, 4, 6
1, 2, 4
总计: 15
2, 5, 8
2, 1, 18
总计: 26
3, 6, 12
总计: 12
4, 5, 12
总计: 12
select
case when grouping(id_1)=1 then null else
case when grouping(code)=1 then '合计' else id_1 end end,
case when grouping(id_1)<>1 then code else '' end,
case when grouping(id_1)=1 then null else
case when grouping(code)=1 then sum(sum_1) else max(sum_1) end end
from table1
group by id_1,code with rollup
having case when grouping(id_1)=1 then null else
case when grouping(code)=1 then '合计' else id_1 end end is not null
order by id_1,code desc
2.Delphi中直接統計,設定其統計屬性即可...
ELSE ISNULL(编号, 'UNKNOWN')
END AS 编号,
CASE WHEN (GROUPING(代码) = 1) THEN 'all'
ELSE ISNULL(代码, 'UNKNOWN')
END AS 代码,
SUM(js) AS js
FROM intdb