SQL 用时方恨少

1.批量插入

INSERT INTO table_name (colum1, colum2, colum3) VALUES 
<foreach collection="list" item="item" index="index" separator=",">
  (#{item.colum1},#{item.colum2},#{item.colum3})
</foreach>

2.批量更新

<foreach collection="list" item="item" index="index" separator=";">
  UPDATE table_name
  <set>
    colum1 = #{item.colum1}, colum2 = #{item.colum2}, colum3 = #{item.colum3}
  </set>
</foreach>

3.外面是单引号，里面使用双引号

<if test='strNum == "1"'>
或者改为
<if test="strNum == '1'.toString() ">

4.PostgreSQL的递归查询(with recursive)

WITH RECURSIVE cte(id, parentid, organizationname,organizationtype) AS
(SELECT id, parentid, organizationname,organizationtype FROM sys_organization WHERE CAST(id as VARCHAR) = #{id} 
UNION ALL
SELECT ch.id, ch.parentid, ch.organizationname,ch.organizationtype FROM sys_organization ch INNER JOIN cte pa ON pa.id = ch.parentid
)
SELECT * FROM cte WHERE organizationtype='XIUXITING' ORDER BY id

#获取指定深度 depth 层级数据
WITH RECURSIVE cte(id, parentid, depth) AS
#初始 depth = 1 即包含自身起计数，where 条件获取第 3 层数据
(SELECT id, parentid, 1 FROM sys_organization WHERE CAST(id as VARCHAR) = #{id} 
UNION ALL
SELECT ch.id, ch.parentid, pa.depth + 1 FROM sys_organization ch INNER JOIN cte pa ON pa.id = ch.parentid
)
SELECT * FROM cte WHERE cte.depth = 3 ORDER BY id

5.SQLServer的递归查询(with cte)

with cte(id,itemcode,pitemcode) as 
(select id,itemcode,pitemcode from t_project
union all
select a.id,a.itemcode,a.pitemcode from t_project a inner join cte b on a.itemcode=b.pitemcode) 
select * from cte

6.关联更新、删除

UPDATE operation o
       JOIN (SELECT o.id, o.status
              FROM operation o
              WHERE o.group = 123
              AND o.status NOT IN ('done')
              ORDER  BY o.parent, o.id LIMIT 1
       ) t
       ON o.id = t.id
SET status = 'applying'

7.查询在一个表存在而另外一个表不存在的数据

select a from table1 A where (select count(1) as num from table2 B where A.a = B.b) = 0

8.使用临时表

select temp.id, temp.name, b.age from (
	select a.id, a.name from tb_a where a.id = 1
) temp
left join on tb_b b on temp.id = b.id
where b.age = 10

9.自定义排序规则

select * from sale_fruit order by case type
    when 'apple' then 1
    when 'banana' then 2
    when 'pear' then 3
    when 'waltermellon' then 4
end asc, price des

10.Excel 批量生成 SQL

=("INSERT INTO data_science.customer_opportunity (col_a,col_b,col_c) VALUES ('"&[@[col_a]]&"', "&"'"&[@[col_b]]&"', "&"'"&[@[col_c]]&"');")

11.统计计算

SELECT USER.user_id,
    COUNT(CASE WHEN res.action = 'pass' THEN 1 END) AS pass_count,
    COUNT(CASE WHEN res.action = 'no' THEN 1 END)  AS no_count,
    COUNT(CASE WHEN res.action = 'pass' THEN 1 END ) / COUNT(*) AS score_rate
FROM
    USER LEFT JOIN res ON USER.user_id = res.user_id 
GROUP BY
    USER.user_id;

12.GROUP BY + HAVING
SELECT [{* | 字段列名}] FROM 表名 GROUP BY 字段名 HAVING 查询条件; HAVING主要用于筛选分组后的结果搭配聚合函数（如COUNT()、SUM()、MIN()、MAX()）一起使用，而WHERE适用于过滤非分组的记录
使用聚合函数展示 user 表中拥有全部 type 的 user_id

SELECT user_id FROM user
WHERE user_id IN (
    SELECT user_id FROM type GROUP BY user_id
    HAVING COUNT(DISTINCT type) = (
        SELECT COUNT(DISTINCT type) FROM type
    )
);