SQL

OR/AND

用括号调整OR和AND的优先级：https://www.cnblogs.com/kenshinobiy/p/4384234.html

AND：所有子条件为TRUE才返回TRUE

• 只要有一个条件为FALSE则返回FALSE
• 部分子条件为UNKNOWN（NULL）其他子条件为TRUE，返回UNKNOWN

OR：只要一个子条件为TRUE返回TRUE

• 所有子条件评估为 FALSE（NULL 会被视为 FALSE）才返回 FALSE

JOIN

left join：https://www.w3school.com.cn/sql/sql_join_left.asp

各种JOIN： https://blog.csdn.net/qq_27484665/article/details/116452128

效率：https://blog.csdn.net/weixin_39634876/article/details/111698250

GROUP

mysql实现分组聚合：

group by：https://zhuanlan.zhihu.com/p/46869970

select ikey,MAX(value) as value from tb1 group by ikey

GROUP_CONCAT

https://blog.csdn.net/qq_35531549/article/details/90383022

https://www.cnblogs.com/zhwbqd/p/4205821.html

Having

having函数：https://blog.csdn.net/weixin_50853979/article/details/124537408

JSON_EXTRACT

SELECT json_extract(`process_context`, '$.fileId') AS fileId FROM TABLE1

CONCAT&CONCAT_WS

select concat(string1, field1, ..., stringN) from TABLE1
select concat_ws(string SEP, string a, string b...)  a:b:c:d...

MySQL≥8.0

窗口函数

# 分组之后取每组之内根据排序字段的第一行值 （根据rownum 随便取）
SELECT *  
FROM (
	SELECT *, ROW_NUMBER() OVER (PARTITION BY [分组字段] ORDER BY [排序字段]) rownum 
  FROM 表名 
) t   
WHERE rownum = 1 # rownum=2取第二行值

SELECT  id
        ,FIRST_VALUE(score) over(partition by id order by LENGTH(score) desc) as score
FROM    tableA
WHERE   gmt_create > '2023-07-12 00:00:00'

# 重复数据取最新的一条记录，对时间进行开窗处理
select * 
from (
  select *, row_number() over(partition by id_card order by gmt_create desc) as rn 
  from table1
)b
where rn = 1

with模块化

with base as (
  select ...
)
select * from base;

PIVOT行转列 UNPIVOT列转行

https://www.jianshu.com/p/1c6fb0df9f58

NULL字段和IN/NOT IN

WHERE field is not null AND field in ('a','b',...)可以简化为WHERE field in ('a','b',...)

WHERE field not in ('a','b',...)应明确为WHERE field is null OR field not in ('a', 'b',...)

field IN ('','',...) 语句，如果该行记录field字段值为 NULL，子条件结果为UNKNOWN，该行记录不会被选中。NULL不等于任何值，包括自身，只能用IS NULL、IS NOT NULL来显式处理 NULL值。