ORACLE-SQL(二)
一、SQL语句
(二)提升篇
1.2.3 分页,分组,计数,排序
/** * 返回数据 * FORGID 医疗机构ID * FRCODE 县区编码 * FORGNAME 医疗机构名称 * FCENNAME_TEM_COUNT 名称不对称计数 * ISNULLCENCODE_COUNT 未对照计数 * TOTALCOUNT L_DIAITEM_ERROR表中共有多少条数据 */ SELECT T3.* FROM (SELECT T2.*, ROWNUM ROWNO FROM (SELECT T.FORGID, T1.FRCODE, T1.FORGNAME, SUM(NVL(T.NCCW, 0)) FCENNAME_TEM_COUNT, SUM(NVL(T.WDZ, 0)) ISNULLCENCODE_COUNT, TOTALCOUNT FROM L_DIAITEM_ERROR T, TORGANIZATION T1 WHERE T.FORGID = T1.FORGID GROUP BY T.FORGID, T1.FORGNAME, T1.FORGSEQ, T1.FRCODE ORDER BY T1.FORGSEQ) T2 WHERE ROWNUM <= V_END) T3 WHERE T3.ROWNO >= V_START;
注意:
a.TOTALCOUNT是已存在的一个变量,这里不作为关注重点;
b.被group by的数据,要想查询表中的某个字段,有且只有两种方式:
方式一:
在group by 后面加上想要查询出来的字段
方式二:
对于数字列,使用求和函数sum()实现查询
否则,会报错:不是group by 表达式
CreateTime--2017年1月20日17:19:04
1.2.4 不同数据库间建立连接
情景:一个库访问另一个库的某张表
UpdateTime--2017年7月17日16:02:26
1.2.7 将查询的计数结果列转行
实例:对两表满足条件的数据进行计数,并横向展示查询结果
sql1:纵向展示(雏形)
SELECT COUNT(1) COUNTRESULT FROM CONSULT_INFO T WHERE T.CON_ORG_ID = '134557' AND T.CONSULT_STATUS = 0 UNION ALL SELECT COUNT(1) COUNTRESULT FROM OUTPATIENT_CONSULT_INFO T2 WHERE T2.CON_ORG_ID = '134557' AND T2.CONSULT_STATUS = 0
查询结果:
sql2:两行两列展示
SELECT COUNT(1) AS COUNTRESULT1, 0 AS COUNTRESULT2 FROM CONSULT_INFO T WHERE T.CON_ORG_ID = '134557' AND T.CONSULT_STATUS = 0 UNION ALL SELECT 0 AS COUNTRESULT1, COUNT(1) AS COUNTRESULT2 FROM OUTPATIENT_CONSULT_INFO T2 WHERE T2.CON_ORG_ID = '134557' AND T2.CONSULT_STATUS = 0
查询结果:
sql3:最终实现
SELECT SUM(COUNTRESULT1) COUNTRESULT1, SUM(COUNTRESULT2) COUNTRESULT2 FROM (SELECT COUNT(1) AS COUNTRESULT1, 0 AS COUNTRESULT2 FROM CONSULT_INFO T WHERE T.CON_ORG_ID = '134557' AND T.CONSULT_STATUS = 0 UNION ALL SELECT 0 AS COUNTRESULT1, COUNT(1) AS COUNTRESULT2 FROM OUTPATIENT_CONSULT_INFO T2 WHERE T2.CON_ORG_ID = '134557' AND T2.CONSULT_STATUS = 0)
查询结果:
本文来自博客园,作者:Marydon,转载请注明原文链接:https://www.cnblogs.com/Marydon20170307/p/6928963.html