解决方案 »
- 求助一个oracle语句
- oracle exp 远程导出数据报错 EXP-00056: 遇到 ORACLE 错误 12514
- 误把oracle9数据库的RED001.LOG删除
- 存储过程出错问题
- SQl语句的基础知识?
- 问一个弱的问题阿?根据E-R图转换得到的表叫什么名字啊?字段表,属性表?谢谢了
- The Network Adapter could not establish the connection
- 请问高手:如何将数据集的某一字段与另一个相同行数的多字段数据集合并?
- 求助为什么会出现最后一个结果重复
- 【pl/sql】一次查询多内网数据库 【请高手指教】
- oracle 如何修改表的字段(列)的排列顺序
- 正则函数问题
WITH T AS
(SELECT 1 ID, 1 FWFS, '100,102' CLR
FROM DUAL
UNION ALL
SELECT 2 ID, 1 FWFS, '100,101,102' CLR
FROM DUAL
UNION ALL
SELECT 3 ID, 1 FWFS, '103' CLR
FROM DUAL
UNION ALL
SELECT 4 ID, 2 FWFS, '102,103' CLR
FROM DUAL)
SELECT CLR, SUM(CLFS1), SUM(CLFS2), SUM(TOTAL)
FROM (SELECT CLR,
DECODE(T2.FWFS, 1, CN, 0) CLFS1,
DECODE(T2.FWFS, 2, CN, 0) CLFS2,
DECODE(T2.FWFS, 1, CN, 0) + DECODE(T2.FWFS, 2, CN, 0) TOTAL
FROM (SELECT CLR, FWFS, COUNT(*) CN
FROM (SELECT ID,
FWFS,
REGEXP_SUBSTR(CLR, '[^,]+', 1, LEVEL) CLR
FROM T
CONNECT BY LEVEL <= REGEXP_COUNT(CLR, ',') + 1
AND ID = PRIOR ID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1
GROUP BY T1.CLR, FWFS) T2) T3
GROUP BY T3.CLR
ORDER BY T3.CLR;