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' )