对一个表的数据进行分类汇总，后在在打印的报表中把结果打印出来

Question

数据库是SQL2000，表结构如下:住院ID号     费用类型     费用名称    规格    数量      单价     日期2004110001    西药费      四环素       100#     100      1.5       2007-01-01
2004110001    西药费      四环素       100#     8        1.5       2007-01-02
2004110001    中药费       人参         2g        76      1         2007-01-02
2004110001    西药费      四环素       100#      1       3         2007-01-02
2004110001    西药费      皮康箱       10ml      2       2.5       2007-01-04
2004110001    中药费      苍耳子        7g        2      0.8        2007-01-02
2004110001    低值耗材    一次性注射器           3       1.4        2007-01-04
2004110001    西药费      皮康箱        10ml     2        4        2007-01-04
2004110001    西药费      滴药水        3ml      23       17       2007-01-05
2004110001    中药费      樟木          10g      12       1         2007-01-05要求报表打印出如下结果:费用名称  规格   数量   单价   金额.四环素    100#    109    1.5   163.5
.皮康箱     10ml   4     2.5    10
.滴药水     3ml    23     1     23
--------------------------------西药费     合计:  196.5元
.
人参       2g      5      70   350
樟木       10g     12     1.2   22.4---------------------------------中药费     小计:   13元..(同上,略).
------------------------------总合计:   元(具体要求: <1>要在报表中体现如上结果，使用的报表控件请说明
      <2>每种相同的药名和规格，数量要合计在一行显示。如四环素
      <3>每种费用类型小计，报表最后有个总合计。)

Answer 1

create table t(
住院ID号 varchar(20),
费用类型 varchar(20),
费用名称 varchar(20),
规格 varchar(10),
数量 int,
单价 decimal(10,2),
日期 datetime)
insert t select '2004110001','西药费','四环素','100#','100','1.5','2007-01-01'
union all select '2004110001','西药费','四环素','100#','8','1.5','2007-01-02'
union all select '2004110001','中药费','人参','2g','76','1','2007-01-02'
union all select '2004110001','西药费','四环素','100#','1','1.5','2007-01-02'
union all select '2004110001','西药费','皮康箱','10ml','2','2.5','2007-01-04'
union all select '2004110001','西药费','苍耳子','7g','2','2.5','2007-01-04'
union all select '2004110001','低值耗材','一次性注射器','','3','1.4','2007-01-04'
union all select '2004110001','西药费','皮康箱','10ml','2','4','2007-01-04'
union all select '2004110001','西药费','滴药水','3ml','23','17','2007-01-05'
union all select '2004110001','西药费','樟木','10g','12','1','2007-01-05'select 费用类型=(case when grouping(费用名称)=1 and grouping(费用类型)<>1 then '合计'
                      when grouping(费用类型)=1 and grouping(费用名称)=1 then '总计'
                      else 费用类型 end),
    费用名称=(case when grouping(费用名称)=1 then '' else 费用名称 end),
    规格=(case when grouping(费用名称)=1 then '' else cast(min(规格) as varchar) end),
    数量=(case when grouping(费用名称)=1 then '' else cast(sum(数量) as varchar) end),
    单价=(case when grouping(费用名称)=1 then '' else cast(avg(单价) as varchar) end),
    金额=sum(单价*数量)
    from t group by 费用类型,费用名称 with rollupdrop table t/*
费用类型                 费用名称                 规格                             数量                             单价                             金额                                       
-------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- 
低值耗材                 一次性注射器                                              3                              1.400000                       4.20
合计                                                                                                                                     4.20
西药费                  苍耳子                  7g                             2                              2.500000                       5.00
西药费                  滴药水                  3ml                            23                             17.000000                      391.00
西药费                  皮康箱                  10ml                           4                              3.250000                       13.00
西药费                  四环素                  100#                           109                            1.500000                       163.50
西药费                  樟木                   10g                            12                             1.000000                       12.00
合计                                                                                                                                     584.50
中药费                  人参                   2g                             76                             1.000000                       76.00
合计                                                                                                                                     76.00
总计                                                                                                                                     664.70*/

Answer 2

没时间整理测试结果了
你自己把SQL执行看结果吧 ~~~

Answer 3

靠~~三连击了~~开马甲开先
还是整理结果吧~~这年头 分难挣啊
测试结果：
费用类型  费用名称     规格  数量    单价       金额    
----------------------------- ---------------------- 
低值耗材 一次性注射器         3    1.400000    4.20
合计                                          4.20
西药费   苍耳子        7g    2     2.500000    5.00
西药费   滴药水        3ml   23    17.000000   391.00
西药费   皮康箱        10ml  4     3.250000    13.00
西药费   四环素        100#  109   1.500000    163.50
西药费   樟木          10g   12    1.000000    12.00
合计                                          584.50
中药费   人参          2g    76    1.000000    76.00
合计                                          76.00
总计                                          664.70

Answer 4

如马甲所示~结果为：
费用类型  费用名称     规格  数量    单价       金额    
----------------------------- ---------------------- 
低值耗材 一次性注射器         3    1.400000    4.20
合计                                          4.20
西药费   苍耳子        7g    2     2.500000    5.00
西药费   滴药水        3ml   23    17.000000   391.00
西药费   皮康箱        10ml  4     3.250000    13.00
西药费   四环素        100#  109   1.500000    163.50
西药费   樟木          10g   12    1.000000    12.00
合计                                          584.50
中药费   人参          2g    76    1.000000    76.00
合计                                          76.00
总计                                          664.70

Answer 5

仔细一看~结果格式不太对，修改一下：
select 费用类型=(case when grouping(费用类型)=1 and grouping(费用名称)=1 then '总计'
                      else 费用类型 end),
    费用名称=(case when grouping(费用名称)=1 and grouping(费用类型)<>1 then '合计'
                   when grouping(费用类型)=1 then ''
                   else 费用名称 end),
    规格=(case when grouping(费用名称)=1 then '' else cast(min(规格) as varchar) end),
    数量=(case when grouping(费用名称)=1 then '' else cast(sum(数量) as varchar) end),
    单价=(case when grouping(费用名称)=1 then '' else cast(avg(单价) as varchar) end),
    金额=sum(单价*数量)
    from t group by 费用类型,费用名称 with rollup/*
费用类型  费用名称     规格  数量    单价       金额    
----------------------------- ---------------------- 
低值耗材 一次性注射器         3    1.400000    4.20
低值耗材 合计                                 4.20
西药费   苍耳子        7g    2     2.500000    5.00
西药费   滴药水        3ml   23    17.000000   391.00
西药费   皮康箱        10ml  4     3.250000    13.00
西药费   四环素        100#  109   1.500000    163.50
西药费   樟木          10g   12    1.000000    12.00
西药费   合计                                  584.50
中药费   人参          2g    76    1.000000    76.00
中药费   合计                                  76.00
总计                                          664.70*/

Answer 6

select 费用类型=case
      when grouping(费用名称)=0 then 费用类型
      when grouping(费用类型)=1 then '总计'
      else '' end,
    费用名称=case
      when grouping(费用名称)=0 then 费用名称
      when grouping(费用名称)=1 and grouping(费用类型)=0 then 费用类型+'合计'
      else '' end,
    规格=case
      when grouping(费用名称)=1 then '' 
      else 规格 end,
    数量=case 
      when grouping(费用名称)=1 then ''
      else 数量 end,
    单价=case when grouping(费用名称)=1 then ''
      else 单价 end,
金额=SUM(数量*单价)
FROM TABLE
GROUP BY 费用类型,费用名称 WITH ROLLUP

Answer 7

create table tb(住院ID号 varchar(20),费用类型 varchar(10),费用名称 varchar(20),规格 varchar(10),数量 int,单价 decimal(18,2),日期 varchar(10))
insert into tb values('2004110001','西药费'  ,    '四环素'     ,  '100#',     100,      1.5,       '2007-01-01')
insert into tb values('2004110001','西药费'  ,    '四环素'     ,  '100#',     8  ,      1.5,       '2007-01-02')
insert into tb values('2004110001','中药费'  ,    '人参'       ,  '2g'  ,      76,      1  ,       '2007-01-02')
insert into tb values('2004110001','西药费'  ,    '四环素'     ,  '100#',      1 ,      3  ,       '2007-01-02')
insert into tb values('2004110001','西药费'  ,    '皮康箱'     ,  '10ml',      2 ,      2.5,       '2007-01-04')
insert into tb values('2004110001','中药费'  ,    '苍耳子'     ,   '7g' ,       2,      0.8,        '2007-01-02')
insert into tb values('2004110001','低值耗材',    '一次性注射器',  '随便写的'    ,     3  ,     1.4 ,       '2007-01-04')
insert into tb values('2004110001','西药费'  ,    '皮康箱'     ,  '10ml',     2   ,     4   ,     '2007-01-04')
insert into tb values('2004110001','西药费'  ,    '滴药水'     ,   '3ml',      23 ,      17 ,      '2007-01-05')
insert into tb values('2004110001','中药费'  ,    '樟木'       ,   '10g',      12 ,      1  ,       '2007-01-05')
goselect 费用名称,规格,数量,单价,金额 from
(
  select * from 
  (
    select 费用类型,费用名称,规格,cast(sum(数量) as varchar) 数量,cast(单价 as varchar) 单价,cast(sum(数量*单价) as varchar) 金额 
    from tb 
    group by 费用类型,费用名称,规格,单价 with rollup
  ) t
  where 单价 is not null
  union all
  select 费用类型,费用名称=费用类型,规格='合计',数量 = '',单价 = '',sum(数量*单价) 金额 from tb group by 费用类型
  union all
  select 费用类型='总合计',费用名称='总合计',规格='',数量 = '',单价 = '',sum(数量*单价) 金额 from tb 
) m
order by 费用类型,case 规格 when '合计' then 2 else 1 enddrop table tb
/*
费用名称                 规格         数量                             单价                             金额                                       
-------------------- ---------- ------------------------------ ------------------------------ ---------------------------------------- 
一次性注射器               随便写的       3                              1.40                           4.20
低值耗材                 合计                                                                       4.20
滴药水                  3ml        23                             17.00                          391.00
皮康箱                  10ml       2                              2.50                           5.00
皮康箱                  10ml       2                              4.00                           8.00
四环素                  100#       108                            1.50                           162.00
四环素                  100#       1                              3.00                           3.00
西药费                  合计                                                                       569.00
樟木                   10g        12                             1.00                           12.00
苍耳子                  7g         2                              0.80                           1.60
人参                   2g         76                             1.00                           76.00
中药费                  合计                                                                       89.60
总合计                                                                                           662.80（所影响的行数为 13 行）*/

Answer 8

create table tb(住院ID号 varchar(20),费用类型 varchar(10),费用名称 varchar(20),规格 varchar(10),数量 int,单价 decimal(18,2),日期 varchar(10))
insert into tb values('2004110001','西药费'  ,'四环素'     ,'100#',   100,1.5,'2007-01-01')
insert into tb values('2004110001','西药费'  ,'四环素'     ,'100#',   8  ,1.5,'2007-01-02')
insert into tb values('2004110001','中药费'  ,'人参'       ,'2g'  ,   76, 1  ,'2007-01-02')
insert into tb values('2004110001','西药费'  ,'四环素'     ,'100#',   1 , 3  ,'2007-01-02')
insert into tb values('2004110001','西药费'  ,'皮康箱'     ,'10ml',   2 , 2.5,'2007-01-04')
insert into tb values('2004110001','中药费'  ,'苍耳子'     ,'7g' ,    2,  0.8,'2007-01-02')
insert into tb values('2004110001','低值耗材','一次性注射器','随便写的',3 ,1.4,'2007-01-04')
insert into tb values('2004110001','西药费'  ,'皮康箱'     ,'10ml',   2  ,4  ,'2007-01-04')
insert into tb values('2004110001','西药费'  ,'滴药水'     ,'3ml',    23 ,17 ,'2007-01-05')
insert into tb values('2004110001','中药费'  ,'樟木'       ,'10g',    12 ,1  ,'2007-01-05')
goselect 费用名称,规格,数量,单价,金额 from
(
  select * from 
  (
    select 费用类型,费用名称,规格,cast(sum(数量) as varchar) 数量,cast(单价 as varchar) 单价,cast(sum(数量*单价) as varchar) 金额 
    from tb 
    group by 费用类型,费用名称,规格,单价 with rollup
  ) t
  where 单价 is not null
  union all
  select 费用类型,费用名称=费用类型,规格='合计',数量 = '',单价 = '',sum(数量*单价) 金额 from tb group by 费用类型
  union all
  select 费用类型='总合计',费用名称='总合计',规格='',数量 = '',单价 = '',sum(数量*单价) 金额 from tb 
) m
order by 费用类型,case 规格 when '合计' then 2 else 1 enddrop table tb/*
费用名称             规格       数量  单价                           金额  
-------------------- ---------- ---- ------------------------------ -----
一次性注射器         随便写的    3     1.40                           4.20
低值耗材             合计                                             4.20
滴药水               3ml        23    17.00                          391.00
皮康箱               10ml       2     2.50                           5.00
皮康箱               10ml       2     4.00                           8.00
四环素               100#       108   1.50                           162.00
四环素               100#       1     3.00                           3.00
西药费               合计                                            569.00
樟木                 10g        12    1.00                           12.00
苍耳子               7g         2     0.80                           1.60
人参                 2g         76    1.00                           76.00
中药费               合计                                             89.60
总合计                                                                662.80
（所影响的行数为 13 行）
*/