sql语句查询

Question

表中有DWMC 单位名称，ZL增量，JZMC 介质名称
——————————————————————————————
DWMC     ZL       JZMC
连铸          30         水
连铸          40         水
连铸          55         电
连铸          66         电
炼钢           88         气
炼钢           99        气
热轧          22          油
热轧          33           油
现在需要按单位名称 ，查出 水 电 气 油的增量ZL总和，如果没有这一项就显示为0效果如下
——————————————————————————————
单位                  水                   电                    气                    油

Answer 1

  1  select dwmc
  2     , sum(decode(jzmc, '水', zl, 0)) 水
  3     , sum(decode(jzmc, '电', zl, 0)) 电
  4     , sum(decode(jzmc, '气', zl, 0)) 气
  5     , sum(decode(jzmc, '油', zl, 0)) 油
  6  from t1
  7* group by dwmc
SQL> /DWMC                               水         电         气         油
-------------------------- ---------- ---------- ---------- ----------
炼钢                                0          0        187          0
热轧                                0          0          0         55
连铸                               70        121          0          0

Answer 2

SELECT DWMC 单位,
       Sum(Decode(JZMC,'水',ZL,0)) 水, 
       Sum(Decode(JZMC,'电',ZL,0)) 电, 
       Sum(Decode(JZMC,'气',ZL,0)) 气, 
       Sum(Decode(JZMC,'油',ZL,0)) 油
FROM tab_name
GROUP BY DWMC