数据操作指令

字符串操作系列

模糊查询

字段 LIKE concat ('%','sd','%')

字符合并

concat ( EXTRACT ( MONTH FROM 时间字段), '-', EXTRACT ( DAY FROM 时间字段) ) AS 字段

截取时间时分秒

SUBSTRING ( to_char( 字段, 'YYYY-MM-DD hh24:mi:ss' ), 12, 5 ) AS 字段

截取字符串最后一位

LEFT( 字段, LENGTH ( 字段) - 1 ) AS 字段

字符串转整数并统计数量

SUM( to_number( 字段, '最大整数值.最大小数值' ) ) AS 字段

SQL中的判断

(CASE 字段 WHEN 条件 THEN 真 ELSE 假 END)AS value,

查询系列

查询后七天数据

字段 BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE

查询当天数据

字段>= date_trunc( 'day', CURRENT_DATE ) 

查询指定时间表

  to_char(字段,'YYYY-MM-DD')='2020-12-09'

查询指定时间段

  字段 BETWEEN to_date('2020-12-09','YYYY-MM-DD')AND to_date('2020-12-12','YYYY-MM-DD')

两表合并

 SELECT DISTINCT
    LEFT( j.roomnum, LENGTH ( j.roomnum ) - 1 ) AS station,
    j.transnum AS transmitterid,
    ( CASE j.transstatus WHEN 1 THEN 1 ELSE 0 END ) AS genre,
    SUBSTRING ( to_char( j.TIME, 'YYYY-MM-DD hh24:mi:ss' ), 12, 5 ) AS "time",
    1 AS ws
    FROM
    get_transdata AS j,
    xml_push_broadstatuschangereport AS x
    WHERE  j.TIME >= date_trunc( 'day', CURRENT_DATE )
    UNION ALL
    SELECT DISTINCT
    x.station,
    x.transmitterid AS transmitterid,
    ( CASE  x.genre WHEN 1 THEN 1 ELSE 0 END ) AS genre,
    SUBSTRING ( to_char( x.startcheckttime, 'YYYY-MM-DD hh24:mi:ss' ), 12, 5 ) AS TIME,
    2 AS ws
    FROM xml_push_broadstatuschangereport AS x,
    get_transdata AS j
    WHERE x.startcheckttime >= date_trunc( 'day', CURRENT_DATE )
    ORDER BY "time" DESC