1.情景展示
一个查询结果,要重复使用多次,也就是对查询结果进行重新筛选,如何实现?
如上图所示,对查询结果A表进行了having截取;
现在,要对A表再次筛选,并将查询结果与having结果进行合并,这个时候,我们就可以对表A进行复用。
2.具体分析
Oracle在9i中引入了with语句。with语句用来给查询语句中的子查询命名,随后就可以在查询语句的其他地方引用这个名称。
格式如下:
WITH AS (subquery_ sql statement)
SELECT
3.解决方案
WITH TMP_TAB AS
(SELECT A.ORGCODE,
(SELECT T.ORGNAME FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) ORGNAME,
(SELECT T.AREA FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) AREA,
SUM(A.YC_BSL) YC_BSL,
SUM(A.SC_BSL) SC_BSL,
SUM(A.YC_BSL) - SUM(A.SC_BSL) AS WC_BSL
FROM (SELECT T.ORGCODE, COUNT(1) YC_BSL, 0 SC_BSL
FROM META_ORG_TABLE T
GROUP BY T.ORGCODE
UNION ALL
SELECT T2.ORGCODE, 0 YC_BSL, COUNT(DISTINCT T3.TABLE_NAME) SC_BSL
FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
WHERE T2.ORGCODE = T3.ORGCODE(+)
AND T2.TABLENAME = T3.TABLE_NAME(+)
AND T3.DATA_NUM > 0
GROUP BY T2.ORGCODE
UNION ALL
SELECT T2.ORGCODE, 0 YC_BSL, 0 SC_BSL
FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
WHERE T2.ORGCODE = T3.ORGCODE(+)
AND T2.TABLENAME = T3.TABLE_NAME(+)
AND T3.DATA_NUM 0
GROUP BY T2.ORGCODE) A
GROUP BY A.ORGCODE)
SELECT *
FROM TMP_TAB
WHERE ORGCODE IN
(SELECT T.ORGCODE
FROM (SELECT DISTINCT ORGCODE FROM META_ORG_TABLE) T,
YLT_ORG_INFO T2
WHERE T.ORGCODE = T2.ORGCODE(+)
AND T2.PARENTORGID =
(SELECT PARENTORGID FROM YLT_ORG_INFO WHERE ORGCODE = '410425'))
UNION ALL
SELECT LISTAGG(B.ORGCODE, ',') WITHIN GROUP(ORDER BY B.ORGCODE) ORGCODE,
'基层医院' ORGNAME,
B.AREA,
SUM(YC_BSL) YC_BSL,
SUM(SC_BSL) SC_BSL,
SUM(WC_BSL) WC_BSL
FROM TMP_TAB B
WHERE B.AREA = '410425'
GROUP BY B.AREA
执行结果:
使用这种方式,比原生的临时表好用得多。
4.拓展
用ibatis如何实现?
分页统计
查看代码
select id="getCountyStatistics" parameterClass="map" resultClass="java.util.HashMap">
isNotNull property="END">
SELECT F.* FROM (
isNotNull>
SELECT E.*, ROWNUM ROWNO FROM (
WITH TMP_TAB AS
(SELECT A.ORGCODE,
(SELECT T.ORGNAME FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) ORGNAME,
(SELECT T.AREA FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) AREA,
SUM(A.YC_BSL) YC_BSL,
SUM(A.SC_BSL) SC_BSL,
SUM(A.YC_BSL) - SUM(A.SC_BSL) AS WC_BSL
FROM (SELECT T.ORGCODE, COUNT(1) YC_BSL, 0 SC_BSL
FROM META_ORG_TABLE T
GROUP BY T.ORGCODE
UNION ALL
SELECT T2.ORGCODE, 0 YC_BSL, COUNT(DISTINCT T3.TABLE_NAME) SC_BSL
FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
WHERE T2.ORGCODE = T3.ORGCODE(+)
AND T2.TABLENAME = T3.TABLE_NAME(+)
[CDATA[
AND T3.DATA_NUM > 0
]]>
GROUP BY T2.ORGCODE
UNION ALL
SELECT T2.ORGCODE, 0 YC_BSL, 0 SC_BSL
FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
WHERE T2.ORGCODE = T3.ORGCODE(+)
AND T2.TABLENAME = T3.TABLE_NAME(+)
[CDATA[
AND T3.DATA_NUM 0
]]>
GROUP BY T2.ORGCODE) A
GROUP BY A.ORGCODE)
SELECT *
FROM TMP_TAB
WHERE ORGCODE IN
(SELECT T.ORGCODE
FROM (SELECT DISTINCT ORGCODE FROM META_ORG_TABLE) T,
YLT_ORG_INFO T2
WHERE T.ORGCODE = T2.ORGCODE(+)
AND T2.PARENTORGID =
(SELECT PARENTORGID FROM YLT_ORG_INFO WHERE ORGCODE = #ORGCODE#))
isNotEmpty prepend="and" property="ORGNAME">
ORGNAME LIKE '%' || #ORGNAME# || '%'
isNotEmpty>
isNotEmpty prepend="UNION ALL" property="AREA">
SELECT LISTAGG(B.ORGCODE, ',') WITHIN GROUP(ORDER BY B.ORGCODE) ORGCODE,
'基层医院' ORGNAME,
B.AREA,
SUM(YC_BSL) YC_BSL,
SUM(SC_BSL) SC_BSL,
SUM(WC_BSL) WC_BSL
FROM TMP_TAB B
WHERE B.AREA = #AREA#
GROUP BY B.AREA
isNotEmpty>
)E
isNotNull property="END">
[CDATA[ WHERE rownum #END# ) F WHERE F.ROWNO >=#START#]]>
isNotNull>
select>
总数查询
查看代码
select id="getCountyStatisticsCount" parameterClass="map" resultClass="java.lang.Integer">
SELECT COUNT(1)
FROM (SELECT 1 AS TEMP_NUM
FROM (SELECT T.ORGCODE, COUNT(1) YC_BSL, 0 SC_BSL
FROM META_ORG_TABLE T
GROUP BY T.ORGCODE
UNION ALL
SELECT T2.ORGCODE,
0 YC_BSL,
COUNT(DISTINCT T3.TABLE_NAME) SC_BSL
FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
WHERE T2.ORGCODE = T3.ORGCODE(+)
AND T2.TABLENAME = T3.TABLE_NAME(+)
[CDATA[
AND T3.DATA_NUM > 0
]]>
GROUP BY T2.ORGCODE
UNION ALL
SELECT T2.ORGCODE, 0 YC_BSL, 0 SC_BSL
FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
WHERE T2.ORGCODE = T3.ORGCODE(+)
AND T2.TABLENAME = T3.TABLE_NAME(+)
[CDATA[
AND T3.DATA_NUM 0
]]>
GROUP BY T2.ORGCODE) A
GROUP BY A.ORGCODE
HAVING A.ORGCODE IN (SELECT T.ORGCODE
FROM (SELECT DISTINCT ORGCODE
FROM META_ORG_TABLE) T,
YLT_ORG_INFO T2
WHERE T.ORGCODE = T2.ORGCODE(+)
AND T2.PARENTORGID =
(SELECT PARENTORGID
FROM YLT_ORG_INFO
WHERE ORGCODE = #ORGCODE#))
isNotEmpty prepend="and" property="ORGNAME">
ORGNAME LIKE '%' || #ORGNAME# || '%'
isNotEmpty>
isNotEmpty prepend="UNION ALL" property="AREA">
SELECT 0 AS TEMP_NUM FROM DUAL
isNotEmpty>
)
select>
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容,欢迎留言!!!
作者:Marydon