17,086
社区成员
发帖
与我相关
我的任务
分享
with t as
(
select '001' seq,'设备1' sb_name,'集团1' jituan,'单位1' units,'F' guoqi,'A' sbtype,'F' waisong,'20110701' playdate FROM DUAL union all
select '002' seq,'设备2' sb_name,'集团1' jituan,'单位2' units,'F' guoqi,'B' sbtype,'F' waisong,'20110604' playdate FROM DUAL union all
select '003' seq,'设备3' sb_name,'集团2' jituan,'单位3' units,'T' guoqi,'C' sbtype,'F' waisong,'20110405' playdate FROM DUAL union all
select '004' seq,'设备4' sb_name,'集团1' jituan,'单位1' units,'F' guoqi,'A' sbtype,'T' waisong,'20110403' playdate FROM DUAL
)
SELECT
SEQ,SB_NAME,units,
SUM(DECODE(SBTYPE,'A',1)) sum_a,
sum(decode(sbtype,'B',1))sum_b,
sum(decode(sbtype,'C',1))sum_c,
sum(decode(GUOQI,'F',1))ok_f,
sum(decode(guoqi,'t',1))ok_t
FROM T
GROUP BY SEQ,UNITS,sb_name;
select
sum(case when 类型='A' and 所在集团='集团1' then 1 else 0 end) "集团1--A类",
sum(case when 类型='A' and 所在集团='集团1' then 1 else 0 end) "集团1--B类",
sum(case when 类型='A' and 所在集团='集团1' then 1 else 0 end) "集团1--C类",
sum(case when 所在集团='集团1' then 1 else 0 end) "集团1合计",
sum(case when 类型='A' and 所在集团='集团2' then 1 else 0 end) "集团2--A类",
sum(case when 类型='A' and 所在集团='集团2' then 1 else 0 end) "集团2--B类",
sum(case when 类型='A' and 所在集团='集团2' then 1 else 0 end) "集团2--C类",
sum(case when 所在集团='集团2' then 1 else 0 end) "集团2合计"
from table_name
where 计划时间 < to_date('2011-08-01','yyyy-mm-dd')
group by 使用单位
order by 使用单位
sum(case when ... then 1 else 0 end)
..
..
..
from
where 计划时间 < to_date('2011-08-01','yyyy-mm-dd')
group by 单位名称
A/B/C分组用
sum(case when ... then 1 else 0 end)
..
..
..
group by 单位名称