SQL
程序员文章站
2022-03-05 16:03:18
...
with 存款临时表(机构编码,余额) as
(select 机构编码,余额 from 存款表 where 余额>200000 )
SELECT T.BANKNAME,
MAX(CASE WHEN T.CONDITIONS=1 THEN T.PEOPLENUM END ) AS level1,
MAX(CASE WHEN T.CONDITIONS=2 THEN T.PEOPLENUM END ) AS level2,
MAX(CASE WHEN T.CONDITIONS=3 THEN T.PEOPLENUM END ) AS level3,
MAX(CASE WHEN T.CONDITIONS=4 THEN T.PEOPLENUM END ) AS level4,
MAX(CASE WHEN T.CONDITIONS=5 THEN T.PEOPLENUM END ) AS level5
FROM
(
(
SELECT 'bankName1' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName2' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName3' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName4' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName5' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName6' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName7' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName8' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName9' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName10' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName11' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName12' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName13' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName14' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
UNION
SELECT 'bankName15' as bankName,
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END as conditions
, Count(*) as peoplenum
FROM 存款临时表 where 机构编码 in (“一些机构号”)
GROUP BY
CASE
WHEN 余额 >200000 and 余额<=500000 THEN 1
WHEN 余额 >500000 and 余额<=1000000 THEN 2
WHEN 余额 >1000000 and 余额<=2000000 THEN 3
WHEN 余额 >2000000 and 余额<=5000000 THEN 4
WHEN 余额 >5000000 THEN 5
END
)
) as T
GROUP BY T.BANKNAME
上一篇： java中List集合及其实现类的方法介绍（附代码）
下一篇： 单一职责原则