前言
? ?那些年我写过的最长sql语句，在此写成博客，以此备份，便于后续工作参考
目录
? ? ? 1.开通用户数
? ? ? 2.开通用户数明细
? ? ? 3.注册用户数
? ? ? 4.注册用户数明细
? ? ? 5.相似博客推荐
? ?1.开通用户数
SELECT W.REGION_NAME AREANAME, A.PROVINCE_NO areaNo, SUBSTR(A.PROVINCE_NO,0,2) cityNO, NVL(A.COUNT, 0) BUSI_HISSUM, NVL(B.COUNT, 0) BUSI_TOD, NVL(C.COUNT, 0) BUSI_YES, D.HISMAX BUSI_HISMAX, E.HISAVG BUSI_HISAVG FROM (SELECT T.PROVINCE_NO, COUNT(T.PROVINCE_NO) COUNT FROM W_BUSI_USER_BIND T WHERE T.SERVICE_TYPE = '01' AND T.ORGAN_CODE IS NOT NULL AND T.PROVINCE_NO IS NOT NULL AND T.BIND_TIME IS NOT NULL GROUP BY T.PROVINCE_NO) A, (SELECT T.PROVINCE_NO, COUNT(T.USER_NO) COUNT FROM W_BUSI_USER_BIND T WHERE T.SERVICE_TYPE = '01' AND T.ORGAN_CODE IS NOT NULL AND T.BIND_TIME IS NOT NULL AND T.PROVINCE_NO IS NOT NULL AND TO_CHAR(T.BIND_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD') GROUP BY T.PROVINCE_NO) B, (SELECT T.PROVINCE_NO, COUNT(T.USER_NO) COUNT FROM W_BUSI_USER_BIND T WHERE T.SERVICE_TYPE = '01' AND T.ORGAN_CODE IS NOT NULL AND T.BIND_TIME IS NOT NULL AND T.PROVINCE_NO IS NOT NULL AND TO_CHAR(T.BIND_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') GROUP BY T.PROVINCE_NO) C, (SELECT A.PROVINCE_NO, MAX(A.USER_NO) HISMAX FROM (SELECT T.PROVINCE_NO, COUNT(T.USER_NO) USER_NO, TO_CHAR(T.BIND_TIME, 'YYYY-MM-DD') FROM W_BUSI_USER_BIND T WHERE T.SERVICE_TYPE = '01' AND T.ORGAN_CODE IS NOT NULL AND T.BIND_TIME IS NOT NULL AND T.PROVINCE_NO IS NOT NULL GROUP BY T.PROVINCE_NO, TO_CHAR(T.BIND_TIME, 'YYYY-MM-DD')) A GROUP BY A.PROVINCE_NO) D, (SELECT A.PROVINCE_NO, FLOOR(AVG(A.USER_NO)) HISAVG FROM (SELECT T.PROVINCE_NO, COUNT(T.USER_NO) USER_NO, TO_CHAR(T.BIND_TIME, 'YYYY-MM-DD') FROM W_BUSI_USER_BIND T WHERE T.SERVICE_TYPE = '01' AND T.ORGAN_CODE IS NOT NULL AND T.BIND_TIME IS NOT NULL AND T.PROVINCE_NO IS NOT NULL GROUP BY T.PROVINCE_NO, TO_CHAR(T.BIND_TIME, 'YYYY-MM-DD')) A GROUP BY A.PROVINCE_NO) E, W_SYS_REGION W WHERE W.REGION_TYPE = '02' AND W.ISACTIVE = '1' AND A.PROVINCE_NO = W.REGION_CODE(+) AND A.PROVINCE_NO = B.PROVINCE_NO(+) AND A.PROVINCE_NO = C.PROVINCE_NO(+) AND A.PROVINCE_NO = D.PROVINCE_NO(+) AND A.PROVINCE_NO = E.PROVINCE_NO(+) ORDER BY W.REGION_CODE
? ?2.开通用户数明细查询
? ??
SELECT a.area_no areaNo, a.count busi_hisSum, NVL(b.count, 0) busi_tod, NVL(c.count, 0) busi_yes, d.count busi_hisMax, e.count busi_hisAvg from (select area_no, count(*) count from w_sync_organ_areas a, w_busi_user_bind b where a.org_no = b.organ_code and exists (select 1 from p_code p where code_type = '60004200' and valid_flag = '1' and a.area_no = p.value) group by area_no) A, (select area_no, count(0) count from w_sync_organ_areas a, w_busi_user_bind b where a.org_no = b.organ_code and to_char(b.bind_time, 'YYYY-MM-DD') = to_char(SYSDATE, 'YYYY-MM-DD') and exists (select 1 from p_code p where code_type = '60004200' and valid_flag = '1' and a.area_no = p.value) group by area_no) B, (select area_no area_no, count(*) count from w_sync_organ_areas a, w_busi_user_bind b where a.org_no = b.organ_code and to_char(b.bind_time, 'YYYY-MM-DD') = to_char(SYSDATE - 1, 'YYYY-MM-DD') and exists (select 1 from p_code p where code_type = '60004200' and valid_flag = '1' and a.area_no = p.value) group by area_no) C, (select s.area_no, Max(s.count) count from (select area_no area_no, TO_CHAR(b.bind_time, 'YYYY-MM-DD'), count(*) count from w_sync_organ_areas a, w_busi_user_bind b where a.org_no = b.organ_code and exists (select 1 from p_code p where code_type = '60004200' and valid_flag = '1' and a.area_no = p.value) group by area_no, to_char(b.bind_time, 'YYYY-MM-DD')) s group by s.area_no) D, (select s.areaNo area_no, floor(AVG(s.count)) count from (select area_no areaNo, to_char(b.bind_time, 'YYYY-MM-DD'), count(*) count from w_sync_organ_areas a, w_busi_user_bind b where a.org_no = b.organ_code and exists (select 1 from p_code p where code_type = '60004200' and valid_flag = '1' and a.area_no = p.value) group by area_no, to_char(b.bind_time, 'YYYY-MM-DD')) s group by s.areaNo) e where a.area_no = b.area_no(+) and a.area_no = c.area_no(+) and a.area_no = d.area_no(+) and a.area_no = e.area_no(+) and a.cityNo is not null order by busi_hisSum DESC
?
?
? ? 3.注册用户数
SELECT A.AREA_NO, SUBSTR(a.area_no,0,2) cityNo, A.NAME AREANAME, NVL(A.CT, 0) HISSUM, NVL(B.CT, 0) TOD, NVL(C.CT, 0) YES, NVL(D.CT, 0) HISAVG, NVL(E.CT, 0) HISMAX FROM (SELECT TMP.NAME, A.AREA_NO, A.CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.AREA_NO, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.CITY_NO IS NOT NULL GROUP BY WU.AREA_NO) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.AREA_NO) A, (SELECT TMP.NAME, A.AREA_NO, A.CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.AREA_NO, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.CITY_NO IS NOT NULL AND TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD') GROUP BY WU.AREA_NO) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.AREA_NO) B, (SELECT TMP.NAME, A.AREA_NO, A.CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.AREA_NO, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.CITY_NO IS NOT NULL AND TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') GROUP BY WU.AREA_NO) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.AREA_NO) C, (SELECT TMP.NAME, A.AREA_NO, MAX(A.CT) CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.AREA_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD') REG_DATE, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.CITY_NO IS NOT NULL GROUP BY WU.AREA_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD')) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.AREA_NO GROUP BY TMP.NAME, A.AREA_NO) D, (SELECT TMP.NAME, A.AREA_NO, FLOOR(AVG(A.CT)) CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.AREA_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD') REG_DATE, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.CITY_NO IS NOT NULL GROUP BY WU.AREA_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD')) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.AREA_NO GROUP BY TMP.NAME, A.AREA_NO) E WHERE A.AREA_NO = B.AREA_NO(+) AND A.AREA_NO = C.AREA_NO(+) AND A.AREA_NO = D.AREA_NO(+) AND A.AREA_NO = E.AREA_NO(+) ORDER BY A.AREA_NO
? ?4.注册用户数明细
SELECT A.CITY_NO area_no, A.NAME AREANAME, SUBSTR(a.city_no,0,2) cityNo, NVL(A.CT, 0) HISSUM, NVL(B.CT, 0) TOD, NVL(C.CT, 0) YES, NVL(D.CT, 0) HISAVG, NVL(E.CT, 0) HISMAX FROM (SELECT TMP.NAME, A.CITY_NO, A.CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.CITY_NO, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.AREA_NO = #areaNo# AND WU.CITY_NO IS NOT NULL GROUP BY WU.CITY_NO) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.CITY_NO) A, (SELECT TMP.NAME, A.CITY_NO, A.CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.CITY_NO, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.AREA_NO = #areaNo# AND WU.CITY_NO IS NOT NULL AND TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD') GROUP BY WU.CITY_NO) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.CITY_NO) B, (SELECT TMP.NAME, A.CITY_NO, A.CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.CITY_NO, COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.AREA_NO = #areaNo# AND WU.CITY_NO IS NOT NULL AND TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') GROUP BY WU.CITY_NO) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.CITY_NO) C, (SELECT TMP.NAME, A.CITY_NO, MAX(A.CT) CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.CITY_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD'), COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.AREA_NO = #areaNo# AND WU.CITY_NO IS NOT NULL GROUP BY WU.CITY_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD')) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.CITY_NO GROUP BY TMP.NAME, A.CITY_NO) D, (SELECT TMP.NAME, A.CITY_NO, FLOOR(AVG(A.CT)) CT FROM CSWEB_GLOBAL.P_CODE TMP, (SELECT WU.CITY_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD'), COUNT(WU.LOGIN_NAME) CT FROM W_USERS WU WHERE WU.AREA_NO = #areaNo# AND WU.CITY_NO IS NOT NULL GROUP BY WU.CITY_NO, TO_CHAR(WU.REG_DATE, 'YYYY-MM-DD')) A WHERE TMP.CODE_TYPE = '60004200' AND TMP.VALUE = A.CITY_NO GROUP BY TMP.NAME, A.CITY_NO) E WHERE A.CITY_NO = B.CITY_NO(+) AND A.CITY_NO = C.CITY_NO(+) AND A.CITY_NO = D.CITY_NO(+) AND A.CITY_NO = E.CITY_NO(+) ORDER BY A.CITY_NO
? ?5.相似博客推荐
??? ? ??1:那些年我写过的存储过程与计划任务
? ? ? ? 2:如何创建数据库链接DBLINK
? ? ? ? 3:统计分析－存储过程
? ? ? ? 4.那些年我写过的最长sql语句
?