日期:2014-05-16 浏览次数:20863 次
SQL> WITH t AS ( 2 SELECT 'wt1' work_type,1 num,100 amount FROM DUAL UNION ALL 3 SELECT 'wt2' work_type,2 num,200 amount FROM DUAL UNION ALL 4 SELECT 'wt3' work_type,3 num,300 amount FROM DUAL UNION ALL 5 SELECT 'wt4' work_type,4 num,400 amount FROM DUAL UNION ALL 6 SELECT 'wt5' work_type,5 num,500 amount FROM DUAL UNION ALL 7 SELECT 'wt6' work_type,6 num,600 amount FROM DUAL 8 ) 9 SELECT 'work_type' work_type, 10 'wt1' wt1, 11 'wt2' wt2, 12 'wt3' wt3 13 FROM DUAL 14 UNION ALL 15 SELECT 'num' num, 16 TO_CHAR(MAX(DECODE(t.work_type, 'wt1', t.num))) wt1, 17 TO_CHAR(MAX(DECODE(t.work_type, 'wt2', t.num))) wt2, 18 TO_CHAR(MAX(DECODE(t.work_type, 'wt3', t.num))) wt3 19 FROM t 20 UNION ALL 21 SELECT 'amount' amount, 22 TO_CHAR(MAX(DECODE(t.work_type, 'wt1', t.amount))) wt1, 23 TO_CHAR(MAX(DECODE(t.work_type, 'wt2', t.amount))) wt2, 24 TO_CHAR(MAX(DECODE(t.work_type, 'wt3', t.amount))) wt3 25 FROM t 26 ; WORK_TYPE WT1 WT2 WT3 --------- ---------------------------------------- ---------------------------------------- ---------------------------------------- work_type wt1 wt2 wt3 num 1 2 3 amount 100 200 300
------解决方案--------------------
select sum(decode(工种,'工种1',1,0)) "工种1", sum(decode(工种,'工种2',1,0)) "工种2", sum(decode(工种,'工种3',1,0)) "工种3", sum(decode(工种,'工种4',1,0)) "工种4", sum(decode(工种,'工种5',1,0)) "工种5" from a union all select sum(decode(工种,'工种1',金额,0)) "工种1", sum(decode(工种,'工种2',金额,0)) "工种2", sum(decode(工种,'工种3',金额,0)) "工种3", sum(decode(工种,'工种4',金额,0)) "工种4", sum(decode(工种,'工种5',金额,0)) "工种5" from a
------解决方案--------------------
建这么一张表: create table A( work_type varchar2(40), person_num number, money number ) 统计sql如下: select '工种' type, sum(decode(work_type,'工种1',person_num,0)) "工种1", sum(decode(work_type,'工种2',person_num,0)) "工种2", sum(decode(work_type,'工种3',person_num,0)) "工种3", sum(decode(work_type,'工种4',person_num,0)) "工种4", sum(decode(work_type,'工种5',person_num,0)) "工种5" from A union select '金额' type, sum(decode(work_type,'工种1',money,0)) "工种1", sum(decode(work_type,'工种2',money,0)) "工种2", sum(decode(work_type,'工种3',money,0)) "工种3", sum(decode(work_type,'工种4',money,0)) "工种4", sum(decode(work_type,'工种5',money,0)) "工种5" from A order by type 1 工种 1 1 1 1 1 2 金额 6 6 6 6 56