10个高级sql写法 – jackyrwj的个人博客 – 愿所有的美好都如期而至

高级 SQL 写法通常指的是使用复杂的 SQL 语句来实现高级数据查询和分析的技巧和方法。 这些高级 SQL 写法可以让用户更方便、更快速地从数据库中获取所需的数据，并且可以进行更复杂的数据分析和处理。 但是需要注意的是，使用高级 SQL 写法也会增加查询的复杂度和难度，需要针对具体的业务场景和数据结构进行优化和调整。在实际的应用中， 需要根据具体的情况选择合适的 SQL 写法，并且需要注意 SQL 查询的性能和效率，避免对数据库造成过大的负担。

本文所讲述 sql 语法都是基于 MySql 8.0

MySql 中的排序 ORDER BY 除了可以用 ASC 和 DESC，还可以通过 ORDER BY FIELD(str,str1,…) 自定义字符串/数字来实现排序。这里用 order_diy 表举例，结构以及表数据展示：

ORDER BY FIELD(str,str1,…) 自定义排序sql如下：

查询结果如下：

如上，我们设置自定义排序字段为 title 字段，然后将我们自定义的排序结果跟在 title 后面。

case when then else end表达式功能非常强大可以帮助我们解决 if elseif else 这种问题，这里继续用 order_diy 表举例，假如我们想在 order_diy 表加一列 level 列，根据money 判断大于60就是高级，大于30就是中级，其余显示低级，sql 如下：

查询结果：

需要注意的就是 case when then 语句不匹配如果没有写 else end 会返回 null，影响数据展示。

我猜大家在日常开发中，应该都对关键词 exists 用的比较少，估计使用 in 查询偏多。这里给大家介绍一下 exists 用法，引用官网文档:

可知 exists 后面是跟着一个子查询语句，它的作用是根据主查询的数据，每一行都放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE），TRUE的话该行数据就会保留，下面用 emp 表和 dept 表进行举例，表结构以及数据展示：

计入我们现在想找到 emp 表中 dept_name 与 dept表 中 dept_name 对应不上员工数据，sql 如下：

查询结果：

我们通过 exists 语法将外层 emp 表全部数据 放到子查询中与一一与 dept 表全部数据进行比较，只要有一行记录返回true。画个图展示主查询所有记录与子查询交互如下：

GROUP_CONCAT(expr) 组连接函数可以返回分组后指定字段的字符串连接形式，并且可以指定排序逻辑，以及连接字符串，默认为英文逗号连接。这里继续用 order_diy 表举例：sql 如下：

查询结果：

如上我们通过 GROUP_CONCAT(title ORDER BY id desc SEPARATOR ‘-‘) 语句，指定分组连接 title 字段并按照 id 排序，设置连接字符串为 -。

自连接查询是 sql 语法里常用的一种写法，掌握了自连接的用法我们可以在 sql 层面轻松解决很多问题。这里用 tree 表举例，结构以及表数据展示：

tree 表中通过 pid 字段与 id 字段进行父子关联，假如现在有一个需求，我们想按照父子层级将 tree 表数据转换成 一级职位 二级职位 三级职位 三个列名进行展示，sql 如下：

结果如下：

我们通过 tree t1 join tree t2 on t1.id = t2.pid 自连接展示 一级职位 二级职位，再用 left join tree t3 on t2.id = t3.pid 自连接展示 二级职位 三级职位，最后通过where 条件 t1.pid = 0过滤掉非一级职位的展示，完成这个需求。

这里继续使用上文提到的 emp 表和 dept 表，数据如下： 可以看到上述 emp 表中 jack 的部门名称与 dept 表实际不符合，现在我们想将 jack 的部门名称更新成 dept 表的正确数据，sql 如下：

查询结果：

我们可以直接关联 emp 表和 dept 表并设置关联条件，然后更新 emp 表的 dept_name 为 dept 表的 dept_name。

ORDER BY 字句中可以跟我们要排序的字段名称，但是当字段中存在 null 值时，会对我们的排序结果造成影响。我们可以通过 ORDER BY IF(ISNULL(title), 1, 0) 语法将 null 值转换成0或1，来达到将 null 值放到前面还是后面进行排序的效果。这里继续用 order_diy 表举例，sql 如下：

查询结果：

MySql 中可以使用 with rollup 在分组统计数据的基础上再进行统计汇总，即用来得到 group by 的汇总信息。这里继续用order_diy 表举例，sql 如下：

查询结果：

可以看到通过 GROUP BY name WITH ROLLUP 语句，查询结果最后一列显示了分组统计的汇总结果。但是 name 字段最后显示为 null，我们可以通过 coalesce(val1, val2, ...) 函数，这个函数会返回参数列表中的第一个非空参数。

查询结果：

with as 语法需要 MySql 8.0以上版本，它有一个别名叫做 CTE，官方对它的说明如下

公用表表达式 (CTE) 是一个命名的临时结果集，它存在于单个语句的范围内，稍后可以在该语句中引用，可以多次引用。

它的作用主要是提取子查询，方便后续共用，更多情况下会用在数据分析的场景上。

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用 with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。这里继续用 order_diy 表举例，这里使用 with as 给出 sql 如下：

查询结果：

这个 sql 查询了 order_diy 表中 money 大于30且小于等于60之间并且 name 是周伯通的记录。可以看到使用 CTE 语法后，sql写起来会简洁很多。

MySql 中通过on duplicate key update语法来实现存在就更新，不存在就插入的逻辑。插入或者更新时，它会根据表中主键索引或者唯一索引进行判断，如果主键索引或者唯一索引有冲突，就会执行on duplicate key update后面的赋值语句。 这里通过 news 表举例，表结构和说数据展示，其中 news_code 字段有唯一索引：

添加sql：

结果如下：

作者：wayn 链接：https://juejin.cn/post/7209625823580766264 来源：稀土掘金 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。