oracle 统计数量sql,统计列中字母的个数-SQL问题

本帖最后由 commitdb 于 2015-7-8 15:20 编辑

SELECT Regexp_Count(Wm_Concat(t.A1), 'A') AS A1,

Regexp_Count(Wm_Concat(t.A2), 'A') AS A2,

Regexp_Count(Wm_Concat(t.A3), 'A') AS A3,

Regexp_Count(Wm_Concat(t.A4), 'A') AS A4,

Regexp_Count(Wm_Concat(t.A5), 'A') AS A5,

Regexp_Count(Wm_Concat(t.A6), 'A') AS A6,

Regexp_Count(Wm_Concat(t.A7), 'A') AS A7,

Regexp_Count(Wm_Concat(t.A8), 'A') AS A8,

Regexp_Count(Wm_Concat(t.A9), 'A') AS A9,

Regexp_Count(Wm_Concat(t.A10), 'A') AS A10,

Regexp_Count(Wm_Concat(t.A11), 'A') AS A11,

Regexp_Count(Wm_Concat(t.A12), 'A') AS A12,

Regexp_Count(Wm_Concat(t.A13), 'A') AS A13,

Regexp_Count(Wm_Concat(t.A14), 'A') AS A14,

Regexp_Count(Wm_Concat(t.A15), 'A') AS A15,

Regexp_Count(Wm_Concat(t.A16), 'A') AS A16,

Regexp_Count(Wm_Concat(t.A17), 'A') AS A17,

Regexp_Count(Wm_Concat(t.A18), 'A') AS A18,

Regexp_Count(Wm_Concat(t.A19), 'A') AS A19,

Regexp_Count(Wm_Concat(t.A20), 'A') AS A20,

Regexp_Count(Wm_Concat(t.A21), 'A') AS A21,

Regexp_Count(Wm_Concat(t.A22), 'A') AS A22

FROM T1 t

UNION ALL

(SELECT Regexp_Count(Wm_Concat(t.A1), 'B'),

Regexp_Count(Wm_Concat(t.A2), 'B'),

Regexp_Count(Wm_Concat(t.A3), 'B'),

Regexp_Count(Wm_Concat(t.A4), 'B'),

Regexp_Count(Wm_Concat(t.A5), 'B'),

Regexp_Count(Wm_Concat(t.A6), 'B'),

Regexp_Count(Wm_Concat(t.A7), 'B'),

Regexp_Count(Wm_Concat(t.A8), 'B'),

Regexp_Count(Wm_Concat(t.A9), 'B'),

Regexp_Count(Wm_Concat(t.A10), 'B'),

Regexp_Count(Wm_Concat(t.A11), 'B'),

Regexp_Count(Wm_Concat(t.A12), 'B'),

Regexp_Count(Wm_Concat(t.A13), 'B'),

Regexp_Count(Wm_Concat(t.A14), 'B'),

Regexp_Count(Wm_Concat(t.A15), 'B'),

Regexp_Count(Wm_Concat(t.A16), 'B'),

Regexp_Count(Wm_Concat(t.A17), 'B'),

Regexp_Count(Wm_Concat(t.A18), 'B'),

Regexp_Count(Wm_Concat(t.A19), 'B'),

Regexp_Count(Wm_Concat(t.A20), 'B'),

Regexp_Count(Wm_Concat(t.A21), 'B'),

Regexp_Count(Wm_Concat(t.A22), 'B')

FROM T1 t)

UNION ALL (SELECT Regexp_Count(Wm_Concat(t.A1), 'C'),

Regexp_Count(Wm_Concat(t.A2), 'C'),

Regexp_Count(Wm_Concat(t.A3), 'C'),

Regexp_Count(Wm_Concat(t.A4), 'C'),

Regexp_Count(Wm_Concat(t.A5), 'C'),

Regexp_Count(Wm_Concat(t.A6), 'C'),

Regexp_Count(Wm_Concat(t.A7), 'C'),

Regexp_Count(Wm_Concat(t.A8), 'C'),

Regexp_Count(Wm_Concat(t.A9), 'C'),

Regexp_Count(Wm_Concat(t.A10), 'C'),

Regexp_Count(Wm_Concat(t.A11), 'C'),

Regexp_Count(Wm_Concat(t.A12), 'C'),

Regexp_Count(Wm_Concat(t.A13), 'C'),

Regexp_Count(Wm_Concat(t.A14), 'C'),

Regexp_Count(Wm_Concat(t.A15), 'C'),

Regexp_Count(Wm_Concat(t.A16), 'C'),

Regexp_Count(Wm_Concat(t.A17), 'C'),

Regexp_Count(Wm_Concat(t.A18), 'C'),

Regexp_Count(Wm_Concat(t.A19), 'C'),

Regexp_Count(Wm_Concat(t.A20), 'C'),

Regexp_Count(Wm_Concat(t.A21), 'C'),

Regexp_Count(Wm_Concat(t.A22), 'C')

FROM T1 t)

UNION ALL (SELECT Regexp_Count(Wm_Concat(t.A1), 'D'),

Regexp_Count(Wm_Concat(t.A2), 'D'),

Regexp_Count(Wm_Concat(t.A3), 'D'),

Regexp_Count(Wm_Concat(t.A4), 'D'),

Regexp_Count(Wm_Concat(t.A5), 'D'),

Regexp_Count(Wm_Concat(t.A6), 'D'),

Regexp_Count(Wm_Concat(t.A7), 'D'),

Regexp_Count(Wm_Concat(t.A8), 'D'),

Regexp_Count(Wm_Concat(t.A9), 'D'),

Regexp_Count(Wm_Concat(t.A10), 'D'),

Regexp_Count(Wm_Concat(t.A11), 'D'),

Regexp_Count(Wm_Concat(t.A12), 'D'),

Regexp_Count(Wm_Concat(t.A13), 'D'),

Regexp_Count(Wm_Concat(t.A14), 'D'),

Regexp_Count(Wm_Concat(t.A15), 'D'),

Regexp_Count(Wm_Concat(t.A16), 'D'),

Regexp_Count(Wm_Concat(t.A17), 'D'),

Regexp_Count(Wm_Concat(t.A18), 'D'),

Regexp_Count(Wm_Concat(t.A19), 'D'),

Regexp_Count(Wm_Concat(t.A20), 'D'),

Regexp_Count(Wm_Concat(t.A21), 'D'),

Regexp_Count(Wm_Concat(t.A22), 'D')

FROM T1 t)

UNION ALL (SELECT Regexp_Count(Wm_Concat(t.A1), 'E'),

Regexp_Count(Wm_Concat(t.A2), 'E'),

Regexp_Count(Wm_Concat(t.A3), 'E'),

Regexp_Count(Wm_Concat(t.A4), 'E'),

Regexp_Count(Wm_Concat(t.A5), 'E'),

Regexp_Count(Wm_Concat(t.A6), 'E'),

Regexp_Count(Wm_Concat(t.A7), 'E'),

Regexp_Count(Wm_Concat(t.A8), 'E'),

Regexp_Count(Wm_Concat(t.A9), 'E'),

Regexp_Count(Wm_Concat(t.A10), 'E'),

Regexp_Count(Wm_Concat(t.A11), 'E'),

Regexp_Count(Wm_Concat(t.A12), 'E'),

Regexp_Count(Wm_Concat(t.A13), 'E'),

Regexp_Count(Wm_Concat(t.A14), 'E'),

Regexp_Count(Wm_Concat(t.A15), 'E'),

Regexp_Count(Wm_Concat(t.A16), 'E'),

Regexp_Count(Wm_Concat(t.A17), 'E'),

Regexp_Count(Wm_Concat(t.A18), 'E'),

Regexp_Count(Wm_Concat(t.A19), 'E'),

Regexp_Count(Wm_Concat(t.A20), 'E'),

Regexp_Count(Wm_Concat(t.A21), 'E'),

Regexp_Count(Wm_Concat(t.A22), 'E')

FROM T1 t)；