sql 随记
- 排序ORDER BY
ORDER BY字句位置是SELECT语句中最后一个字句
降序：DESC 升序：ASC(默认)
- WHERE 语句
操作符：= <> != < <= !< > >= !> BETWEEN (在指定两个值之间)
ISS NULL (为NULL值)
2.1 AND操作符
2.2 OR操作符（AND操作符优先级大于OR）
- IN操作符
- NOT操作符
- LIKE操作符
5.1 百分号(%)通配字符
表示任意字符出现任意次数
5.2 下划线(_)通配符
与%一样，但_只匹配单个字节
- 方括号([])通配符
用来指定一个字符集，他必须匹配指定位置(通配符位置)的一个字符，可以用前缀
- 字段
RTRIM() 函数去掉右边所有空格，LTRIM()函数去掉串左边空格，TRIM()函数去掉串左右两边空格。
拼接字段：用 “+”或“||” MYSQL用CONCAT( , ) lg: name+’(’+sex+’)’ 或name||’(’||sex||’)’
获取当前时间：Access使用NOW() ;DB2和PostgreSQL使用CURRENT_DATE;MySQL使用CURDATE();Oracle使用SYSDATE;SQL使用GETDATE()。
提取串组成部分:Access使用MID();Oracle和PostgreSQL使用SUBSTR();MySQL、SQL Server和Sybase使用SUBSTRING()
数据类型转化：Access和Oracles使用多个函数，每种类型的转化有一个函数；DB2和PostgreSQL使用CAST();MySQL和SQL Server、Sybase使用CONVERT()
- 常用文本处理函数
LEFT() 返回串左边字符
LENGTH()（DATELENG()、LEN()） 返回串长度
LOWER()（Access使用LCASE() ） 将串转为小写
SOUNDEX() 返回串的SOUNDEX值
UPPER()（Access()使用UCASE()） 将串转化为大写
- 聚集函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
10. 分组 GROUP BY
HAVING过滤分组（GROUP BY后面）
11. 取别名：Oralcel没有AS关键字
12.环比时间：
环比时间（单位：天）可以把天转化为 秒，分，时，周，月，年 来计算
TO_NUMBER(TO_CHAR(TO_DATE(TIME_ID,'YYYYMMDDHH24MI')-1,'YYYYMMDD')||'0000') HTIME,
Lg:
WITH T_TABLE AS (
SELECT T.TIME_ID,
TO_CHAR(TO_DATE(T.TIME_ID, 'YYYYMMDDHH24MI') - 1, 'YYYYMMDDHH24MI') HTIME,
NULLIF(TO_NUMBER(T1), 0) VAL
FROM IPMSODM.O_VOBB_D T
WHERE T.TIME_ID <= '201712130000'
and t.ip='10.238.177.34'
and t.SYSTEM_NAME='KLISBC4'
)
SELECT TO_CHAR(TO_DATE(C.TIME_ID,'YYYYMMDDHH24MI'),'YYYY-MM-DD'), C.VAL, ROUND(100*(C.VAL-H.VAL)/NULLIF(H.VAL,0),2) HB
FROM T_TABLE C LEFT JOIN T_TABLE H
ON C.HTIME= H.TIME_ID
WHERE rownum <=15
ORDER BY C.TIME_ID
附着失败次数(用户原因)：sum(case when procedure_type=1 and procedure_status in (2,3) and fail_cause_code in (3,5,6,7,8,15,19) then 1 else 0 end)
13 .平滑查询
SELECT TO_CHAR(TO_DATE(REPLACE(TIME_ID, SUBSTR(TIME_ID, 1, 8), 20171126),'YYYYMMDDHH24MI'),'YYYY-MM-DD HH24:MI'),
AREA_NAME,
IMEI_TAC,
TERMINAL_BRAND,
TERMINAL_MODEL,
HTTP_SUC_RATE,
HTTP_DELAY,
HTTP_DLRATE_AVG,
HTTP_DLRATE
FROM DM_OWNAPP_TERMINAL A
WHERE
A.TIME_ID = '201707030000'
AND A.AREA_NAME =？
AND (HTTP_SUC_RATE < 70 OR HTTP_DELAY > 500 OR HTTP_DLRATE_AVG < 100 OR
HTTP_DLRATE < 300)
AND ROWNUM < 200
ORDER BY DBMS_RANDOM.VALUE()
- 工单查询（非实时派单）
if("jk".equals(flag)){
tWhere= "AND T.ALARMFROM_TYPE_EN = 'alarmFromType_groupCusEte' ";
tWhere1= "AND TYPE='type_jk_area' ";
}
WITH O AS
(
SELECT T.ORDER_CITY, T.INDEX_NAME, T.ORDER_NENAME
FROM O_OM_TASK_EC_ORDER T
WHERE T.ORDER_ID NOT IN
(
SELECT T.ORDER_ID
FROM O_OM_TASK_EC_ORDER T
WHERE T.ORDER_STATUS = '已归档'
)
--AND T.ORDER_STATUS = '运行中'
AND T.REPLY_TIME IS NULL
AND T.INDEX_NAME IS NOT NULL
AND T.ALARMFROM_TYPE_EN IN ('alarmFromType_mobileEte','alarmFromType_internetOfThings')
UNION
SELECT T.ORDER_CITY, T.INDEX_NAME, T.ORDER_NENAME -- //归了档但归档时间到当前时间小于2天
FROM O_OM_TASK_EC_ORDER T
WHERE T.ORDER_STATUS = '已归档'
AND T.ALARMFROM_TYPE_EN IN ('alarmFromType_mobileEte','alarmFromType_internetOfThings')
AND T.INDEX_NAME IS NOT NULL
AND TRUNC(SYSDATE)- TRUNC(T.REPLY_TIME)<2
), Q AS(
SELECT * FROM ( --//求相同派单对象最近派单过的工单号
SELECT T.ORDER_ID, T.ORDER_CITY, T.INDEX_NAME, T.ORDER_NENAME,
RANK() OVER(PARTITION BY T.ORDER_CITY, T.INDEX_NAME, T.ORDER_NENAME ORDER BY T.REPLY_TIME DESC) T_ROWNUM
FROM O_OM_TASK_EC_ORDER T
WHERE T.INDEX_NAME IS NOT NULL AND T.ORDER_STATUS = '已归档'
AND T.ALARMFROM_TYPE_EN IN ('alarmFromType_mobileEte','alarmFromType_internetOfThings')
) WHERE T_ROWNUM=1
)
SELECT TYPE, AREA_NAME, CITY_NAME,
C.INDEX_NAME, INDEX_UNIT, INDEX_VALUE, THRESHOLD, C_SYMBOL,
ALARMOBJ_NAME, ALARM_TITLE, RULE, ORDER_DESC, C.DETAIL_VALUE, Q.ORDER_ID
FROM DM_NRTM_SEND_ORDER_H C
LEFT JOIN Q ON Q.ORDER_CITY = C.AREA_NAME
AND Q.INDEX_NAME = C.INDEX_NAME
AND Q.ORDER_NENAME = C.ALARMOBJ_NAME
WHERE NOT EXISTS
(
SELECT * FROM O
WHERE O.ORDER_CITY = C.AREA_NAME
AND O.INDEX_NAME = C.INDEX_NAME
AND O.ORDER_NENAME = C.ALARMOBJ_NAME
)
AND TYPE<>'type_jk_area'
AND ORDER_TIME= '201710271200'
- 工单查询（非实时派单工单稽核）
SELECT ORDER_ID, TEMP_TYPE_EN, KEY, INDEX_NAME, QUERY_TIME FROM
(
SELECT ORDER_ID,
T.ORDER_CITY||','||ORDER_NENAME KEY,
T.TEMP_TYPE_EN, T.INDEX_NAME,
TO_CHAR(SYSDATE - 1, 'YYYYMMDD') || '0000' QUERY_TIME -- // 用回单时间的第二天的指标进行回填,此处用SYSDATE - 1而不是用T.REPLY_TIME + 1，是为了排除第一次稽核无指标就一直不能回填的情况
FROM O_OM_TASK_EC_ORDER T
WHERE TO_CHAR(SYSDATE, 'YYYYMMDD') > TO_CHAR(T.REPLY_TIME + 1, 'YYYYMMDD') -- //为了能取到指标。因为当天只能查前一天的数据
AND T.AFT_INDEX_VALUE IS NULL AND T.INDEX_NAME IS NOT NULL
AND T.INDEX_VALUE IS NOT NULL AND T.ORDER_STATUS = '已归档'
AND T.TEMP_TYPE_EN||'-'||T.INDEX_NAME IN ('type_badUrl-HTTP访问成功率')
UNION ALL
SELECT ORDER_ID,
T.ORDER_CITY||','||ORDER_NENAME KEY,
T.TEMP_TYPE_EN, T.INDEX_NAME,
TO_CHAR(SYSDATE - 1, 'YYYYMMDD') || '0000' QUERY_TIME -- //因为当天只能查前一天的数据
FROM O_OM_TASK_EC_ORDER T
WHERE
(
(T.THRESHOLD_FLAG = '>' AND T.AFT_INDEX_VALUE > T.INDEX_THRESHOLD)
OR
(T.THRESHOLD_FLAG = '<' AND T.AFT_INDEX_VALUE < T.INDEX_THRESHOLD)
) AND T.INDEX_NAME IS NOT NULL AND T.INDEX_VALUE IS NOT NULL
AND T.AFT_INDEX_VALUE IS NOT NULL AND T.ORDER_STATUS = '已归档'
AND T.TEMP_TYPE_EN||'-'||T.INDEX_NAME IN ('type_badUrl-HTTP访问成功率')
) ORDER BY TEMP_TYPE_EN, QUERY_TIME
对于表中两行记录完全一样的情况，可以用下面语句获取到去掉重复数据后的记录：
select distinct * from 表名
14.MySql查询NOT EXISTS使用栗子：解释：查询a表排除在b表中的某些条件值数据
select *
from tb_videotape_label a
WHERE NOT EXISTS
(
select * from tb_download_record b
where a.id = b.label_id
and b.download_status = 1
)
and timestampdiff(minute,a.begintime,a.endtime)>60
