表Table无主键,根据“字段3”的值来划分类别如:'01','02', '05'属于类别1,;'02', '05'属于类别2;
字段1 字段2 字段3 字段4
1040 201106 01 102
1040 201106 05 111
1040 201106 04 17
2290 201106 02 13
2290 201106 03 100
2290 201106 05 19
对表进行汇总,汇总结果如下:
字段1 字段2 类别1 类别2
1040 201106 213 111
2290 201106 32 32
字段1 字段2 字段3 字段4
1040 201106 01 102
1040 201106 05 111
1040 201106 04 17
2290 201106 02 13
2290 201106 03 100
2290 201106 05 19
对表进行汇总,汇总结果如下:
字段1 字段2 类别1 类别2
1040 201106 213 111
2290 201106 32 32
with t as (
select '1040' a, '201106' b,'01' c,'102' d from dual
union all
select '1040' a, '201106' b,'05' c,'111' d from dual
union all
select '1040' a, '201106' b,'04' c,'17' d from dual
union all
select '2290' a, '201106' b,'02' c,'13' d from dual
union all
select '2290' a, '201106' b,'03' c,'100' d from dual
union all
select '2290' a, '201106' b,'05' c,'19' d from dual
)select t.a,t.b,sum(decode(t.c,'01',t.d,'02',t.d,'05',t.d,0)),sum(decode(t.c,'02',t.d,'05',t.d,0))
from t
group by t.a,t.b
order by t.a
FROM M_ZF_COMBINATION_STAT T
WHERE T.M_CLASS IN ('11', '12', '15')
GROUP BY T.PER_TYPE
ORDER BY T.PER_TYPE
结果如下,共28行:
1 11 65531
2 12 325
3 21 156485
4 22 1039
5 25 12432
6 26 26
7 35 0
8 36 5
9 40 612
10 41 707
11 42 317
12 53 0
13 61 2258
14 62 7
15 63 173
16 65 7
17 71 1653
18 72 0
19 73 114
20 74 0
21 75 8
22 77 0
23 81 992
24 82 1
25 83 38
26 85 0
27 89 12
28 92 0SELECT T.PER_TYPE,
SUM(DECODE(T.M_CLASS,
'11',
T.INH_DAYS,
'12',
T.INH_DAYS,
'15',
T.INH_DAYS,
0)) INH_DAYS
FROM M_ZF_COMBINATION_STAT T
GROUP BY T.PER_TYPE
ORDER BY T.PER_TYPE
结果如下,共33行:
1 11 65531
2 12 325
3 13 0
4 21 156485
5 22 1039
6 25 12432
7 26 26
8 35 0
9 36 5
10 40 612
11 41 707
12 42 317
13 53 0
14 61 2258
15 62 7
16 63 173
17 65 7
18 66 0
19 68 0
20 71 1653
21 72 0
22 73 114
23 74 0
24 75 8
25 76 0
26 77 0
27 81 992
28 82 1
29 83 38
30 85 0
31 86 0
32 89 12
33 92 0这两个有何区别,谢谢!