对一个表进行出分类汇总

Question

表结构如下:住院ID号      费用类     费用名称           金额2004110001    住院护理    指导母乳喂养       100
2004110001    中药费       人参               76
2004110001    西药费      四环素             3
2004110001    中药费      苍耳子              2
2004110001    低值耗材    一次性注射器        3
2004110001    西药费      皮康箱              4
2004110001    西药费      滴药水             17
2004110001    中药费      樟木              12
.....
要求查询结果:  (报表)人参     76
樟木     12
.
.中药费     合计:   13
.
.
.
西药费     合计:  13323
.
...
总合计:   

Answer 1

select c2,c3
from
(
select 费用类 c1，费用类+'合计' c2,sum(金额) c3
from t
group by 费用类
union all
select 费用类,费用名称,金额 from t
) tt
order by tt.c1

Answer 2

select * from t
order by type
compute sum(qty)by type
compute sum(qty)
ID                   type                 name                 qty         
-------------------- -------------------- -------------------- ----------- 
2004110001           中藥費                  人參                   76
2004110001           中藥費                  蒼耳子                  2
2004110001           中藥費                  樟木                   12                                                               sum
                                                               ===========
                                                               90
ID                   type                 name                 qty         
-------------------- -------------------- -------------------- ----------- 
2004110001           西藥費                  皮康箱                  4
2004110001           西藥費                  滴藥水                  17
2004110001           西藥費                  四環素                  3                                                               sum
                                                               ===========
                                                               24
ID                   type                 name                 qty         
-------------------- -------------------- -------------------- ----------- 
2004110001           住院護理                 指導母乳喂養               100                                                               sum
                                                               ===========
                                                               100
ID                   type                 name                 qty         
-------------------- -------------------- -------------------- ----------- 
2004110001           低值耗材                 一次性注射器               3                                                               sum
                                                               ===========
                                                               3
                                                               sum
                                                               ===========
                                                               217
(13 row(s) affected)

Answer 3

为什么MSSQLSERVER就没用象ACCESS这样的功能，象在ACCESS这问题就简单多了，直接有视图看视化操作就可以完成了，也不用再编程这么麻烦

Answer 4

Top  
 fa_ge(非学无以广才,非志无以成学) 这样会生成多个表,我只想要一个查询结果表,怎么办?

Answer 5

TO :jiazheng(飛天) 
在显示结果时出现混乱,能否改成统一格式,就是 一项项后再合计

Answer 6

以下是运行jiazheng(飛天) 的代码后的结果:
材料费..........合计
18.1000 一次性口帽
1.5000 一次性手套
1.5000 婴儿爽身粉
1.0000 婴儿洗发液
1.0000 婴儿沐浴液
1.0000 大浴巾
2.0000 小浴巾
1.0000 一次性中单
5.0000 一次性口帽
1.5000 一次性手套
1.5000 大棉球
.1000 新洁尔灭液
1.0000 终末处置费
2.0000 阴查
10.0000 处置治疗..........合计
14.0000 终末处置费
2.0000 理疗
20.0000 骨科理疗..........合计
20.0000 挂号诊疗..........合计
30.0000 120出诊
30.0000 水、电费
2.0000 其他费..........合计
2.0000 西药费..........合计
23.0000 西药费
12.0000 西药费
11.0000 中药费
2.8000 中药费
139.2000 中药费
423.5000 中药费
278.4000 中药费
278.4000 中药费
278.4000 中药费
232.0000 中药费..........合计
1632.7000 住院护理..........合计
33.0000 婴儿洗澡费
1.0000 指导母乳喂养
5.0000 婴儿基础护理费
3.0000 特护
24.0000