sql注意

1.先分页，再排序        

                 以下是先对表进行分页搜索，如搜索出来的结果是： 

                  a.在100条数据中的前10条数据集合A

                  b.再对集合A进行排序

 SELECT *
                        FROM (
                              SELECT 
                               MOVIE_UID AS PICTURE_RELATE_ID,
                              TITLE AS HOT_MOVIE_CACHE_NAME,
                              PICTURE_PATH,
                              START_TIME AS DATEINDATE_IN,
                              ROWNUM AS rownumber,
                              PICCACHEPATH
                              FROM 
                              V_SOON_CINEMA
                              WHERE
                              MOVIE_STATE = '$arg_movieState'
                              AND
                              (PICTURE_TYPE ='$arg_pictureType'
                               OR PICTURE_TYPE ='封面')
                              )
                       WHERE rownumber BETWEEN '$pageStar' AND '$pageEnd'
                       ORDER BY PICTURE_RELATE_ID DESC

2.先排序，再分页

             

               以下是先对表进行排序搜索，如搜索出来的结果是： 

                  a.在100条数据中的先倒序排列得到数据集合B

                  b.再对集合B从中搜索出前10条来

    SELECT * FROM
                       (SELECT
                        vsc.MOVIE_UID AS PICTURE_RELATE_ID,
                        vsc.TITLE AS HOT_MOVIE_CACHE_NAME,
                        vsc.PICTURE_PATH,
                        vsc.START_TIME AS DATEINDATE_IN,
                        PICCACHEPATH,
                        ROWNUM row_num from
                        (SELECT MOVIE_UID,TITLE,PICTURE_PATH,START_TIME,PICCACHEPATH FROM V_SOON_CINEMA vs
                        WHERE
                        MOVIE_STATE = '$arg_movieState'
                        AND
                        (PICTURE_TYPE ='$arg_pictureType'
                         OR PICTURE_TYPE ='封面')
                        ORDER BY vs.MOVIE_UID DESC) vsc
                        WHERE ROWNUM<='$pageEnd'
                        )vsca WHERE vsca.row_num >= '$pageStar'"

3.sql  to_char 与to_timestamp 例子

SELECT CONCAT('￥',PRICE_STANDARD) as PRICE,to_char(SHOW_DETAIL_TIME,'HH24:MI') as SHOWTIME,
              SHOW_DATE,to_char(SHOW_DATE,'yyyy-mm-dd') as STR_DATE
              FROM V_SHOW_FILM_CINEMA
              WHERE FILM_UID = '$arg_moveId'
              AND SHOW_DETAIL_TIME > to_timestamp('$nowTime','yyyy-mm-dd HH24:mi:ss')
              ORDER BY SHOW_DATE,SHOWTIME

4.sql not exist 与not in

                not in 当T_GROUP_CINEMA表没数据时，下面搜索不出数据

                相当于select ....not in(null)

SELECT DISTINCT T_CINEMA.CINEMA_ENTITY_ID,T_CINEMA.CINEMA_NAME
        FROM T_CINEMA,T_GROUP_CINEMA
        WHERE
        T_CINEMA.CINEMA_ENTITY_ID 
        not in(SELECT CINEMA_ENTITY_ID FROM T_GROUP_CINEMA);

          当T_GROUP_CINEMA表没数据依然可以查询出数据来

select T_CINEMA.CINEMA_ENTITY_ID,T_CINEMA.CINEMA_NAME 
from T_CINEMA
where not exists 
(select 1 
        from T_GROUP_CINEMA 
        where 
        T_GROUP_CINEMA.CINEMA_ENTITY_ID=T_CINEMA.CINEMA_ENTITY_ID 
)