01-用一条sql统计同一张表中满足不同条件的个数

SELECT
            t."TradeTime",
            t."shiftDate",
            ( to_char( t."TradeTime", 'hh24' ) + 1 ) AS "hour",
            SUM( CASE WHEN t."tradeResult" = '1' THEN 1 ELSE 0 END ) AS "tradeSuccessfully",
            sum( CASE WHEN t."tradeResult" IN ( '1', '2', '3', '4' ) THEN 1 ELSE 0 END ) AS "tradeall",
            sum( CASE WHEN t."logicalFailCause" IN ( '1005', '1007', '1021', '1012' ) THEN 1 ELSE 0 END ) AS "position" 
        FROM
            "TB_DS_TRADEINFO" t 
        WHERE
            to_char( t."TradeTime", 'yyyy-mm-dd' ) = ( to_char( SYSDATE - 1, 'yyyy-mm-dd' ) ) 
        GROUP BY
            t."TradeTime",
            t."shiftDate"

02-oracle经典sql

-----年度和季度sql---------------
--获取当前时间季度
select  to_char( SYSDATE, 'q' )  from  dual ;
--获取当前时间年份
select    to_char( SYSDATE, 'yyyy' )  FROM    dual;
--获取当前时间年度和当前季度
SELECT
    to_char( SYSDATE, 'yyyy' ),
    to_char( SYSDATE - interval '12' month, 'q' ) 
FROM
    dual;
--获取上个季度是哪个季度
select  to_char( SYSDATE - interval '3' month, 'q' )   from  dual;
--获取当前时间上一年的同季度
select to_char( SYSDATE - interval '12' month, 'q' )   from  dual;
--获取当前时间上一年度年份
select to_char( SYSDATE - interval '12' month, 'yyyy' )  from  dual;
--获取当前时间上年度的年份和季度
SELECT
    to_char( SYSDATE - interval '12' month, 'yyyy' ),
    to_char( SYSDATE - interval '12' month, 'q' ) 
FROM
    dual;
--上个季度的年份
select TO_CHAR( SYSDATE - interval '3' month, 'yyyy' )  from  dual;
--获取当前年度当前季度的数据,date为表中的字段，类型为date类型
select * from  dual where    
    to_char(date, 'yyyy' ) = TO_CHAR( SYSDATE, 'yyyy' ) 
    AND to_char(date, 'q' ) = to_char( SYSDATE, 'q' ) 
--获取本年度的数据,date为表中的字段，类型为date类型
select * from  dual  where to_char(date, 'yyyy' ) = TO_CHAR( SYSDATE, 'yyyy' ) 
--获取上一年度的数据,date为表中的字段，类型为date类型
select * from dual where to_char(date, 'yyyy' )=TO_CHAR(ADD_MONTHS(SYSDATE,- 12 ),'yyyy' ) 
---------------------月份和日期sql-------------------------------------
--获取当前时间日期(几号)
select    to_char( SYSDATE, 'dd' )  FROM    dual;
--获取当前时间月份
select    to_char( SYSDATE, 'mm' )  FROM    dual;
--获取当前时间日期(年月日)
select    to_char( SYSDATE, 'yyyy-mm-dd' )  FROM    dual;
--获取前一天的日期(年月日)
select  to_char( SYSDATE - 1, 'yyyy-mm-dd' ) from dual;
--获取后一天的日期(年月日)
select  to_char( SYSDATE +1, 'yyyy-mm-dd' ) from dual;
--获取上一个月的日期
select to_char(add_months(trunc(sysdate),-1),'yyyy-mm-dd') from dual;
--获取下个月的日期
select to_char(add_months(trunc(sysdate),1),'yyyy-mm-dd') from dual;
--获取当前时间的上一年的日期
select    TO_CHAR(add_months( trunc( SYSDATE ),- 12 ), 'yyyy-mm-dd' )  FROM    dual;
--获取当前时间的下一年的日期
select    TO_CHAR(add_months( trunc( SYSDATE ),+ 12 ), 'yyyy-mm-dd' )  FROM    dual;

03-oracle给查询结果一个虚拟自增的列

-- t."shiftDate" 是 order by 后面的排序字段
SELECT
    row_number ( ) over ( ORDER BY to_char( t."shiftDate", 'mm' ) ) 月数,
    t."stationId",
    to_char( t."shiftDate", 'yyyy-mm-dd' )
FROM
    "TB_DS_TRADEINFO" t 
ORDER BY
    to_char( t."shiftDate", 'mm' )