oracle 数据库语句简记

来源：互联网发布：java程序员怎么接私活编辑：程序博客网时间：2024/04/28 17:14

</pre><pre code_snippet_id="1957862" snippet_file_name="blog_20161031_2_2452191" name="code" class="sql"><pre name="code" class="sql">ALTER SESSION SET CURRENT_SCHEMA=p2p;select ym,nvl(counth,0) zc,nvl(countt,0) tz from (    SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), ROWNUM - 1),'yyyy-MM')  as ym FROM DUAL    CONNECT BY ROWNUM <= months_between(to_date('2016-09', 'yyyy-MM'), to_date('2014-10', 'yyyy-MM')) + 1) left join(       select to_char(REG_TIME,'yyyy-mm') monthh,count(1) counth from  ACC_USER_REG     where REG_TIME < '2016-9-30 23.59.59'     group by to_char(REG_TIME,'yyyy-mm')) monthD on monthD.monthh = ym left JOIN (    select   monthtt,count(1) countt from(        SELECT distinct   * from(            SELECT distinct tt.AU_ID ,to_char(tt.DATEPOINT,'yyyy-mm') monthtt from (                SELECT  AU_ID , DATEPOINT , TT_ID , STATUS FROM TS_TENDER tt                where not exists(select tt_id from TS_CANCEL_TENDER tct where tct.tt_id = tt.tt_id and tct.status = 1 ) and tt.status = 1                union all                SELECT AU_ID , DATEPOINT , TT_ID , STATUS FROM banhuitong.TS_TENDER ) tt             where not exists(select tt_id from banhuitong.TS_CANCEL_TENDER tct where tct.tt_id = tt.tt_id and tct.status = 1 ) and tt.status = 1                          union all                      SELECT distinct AU_ID , to_char(DATEPOINT,'yyyy-mm') monthtt from  (                SELECT   AU_ID , DATEPOINT FROM TS_CREDIT_ASSIGN where STATUS = 1                union all                SELECT  AU_ID , DATEPOINT FROM banhuitong.TS_CREDIT_ASSIGN where STATUS = 1          )         )    )where monthtt < '2016-10'     group by monthtt) monthT on monthT.monthtt =ym order by  ym;

1.因为查询表不属于同一用户，该sql用到两种方法：

ALTER SESSION SET CURRENT_SCHEMA=p2p;

或者直接

STATUS FROM banhuitong.TS_TENDER

2.下面这条语句是获取两个时间段所有的月份的。 “2014-10”这两块需要保持一致，后一个“2014-10”与前面时间控制时间间隔长度，前一个是为了控制时间开始时间，

也就是说如果后两个改为 “2015-09” 和“2013-10” 查询出来还是从“2014-10”到“2016-09”。

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), ROWNUM - 1),'yyyy-MM')  as ym FROM DUAL    CONNECT BY ROWNUM <= months_between(to_date('2016-09', 'yyyy-MM'), to_date('2014-10', 'yyyy-MM')) + 1

3.个人问题，数据库查询不要使用不等于，要用 not exists()

1 0