mysql在⼦查询中使⽤⾃定义变量和条件语句实现函数效果的查询语句mysql在⼦查询中使⽤⾃定义变量和条件语句实现函数效果的查询语句
简单的mysql语句前⾔
mysql语法简单，但也正因如此，在检索稍微复杂的数据时，有时可能需要连续的⼦查询的嵌套以及表连接的组合应⽤，其逻辑上的循环嵌套远不如通过中间变量来存储易于理解。但⼀般情况下，我们倾向于通过⼀个分号来输出⼀次查询结果，⽽不是保存为视图进⾏⼆次查询。这样的情况下，对于不是特别复杂的检索逻辑我们可以尽量通过⾃定义变量来存储值，达到编程语⾔中的函数效果，⽽不是完全依
靠where⼦句进⾏⼀箩筐的添加条件让机器思考，这样既易于编写⼜提升了速度。
⾃定义变量和条件语句语法规则
⾃定义变量规则
@变量名:=固定值 | 函数调⽤ | 条件语句别名
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;  # 初始化变量
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; # 引⽤变量
条件语句
在⾮funtion和procedure的⼀般查询语法中，sql中的条件语句和⾼级编程语⾔中的条件语句⽤法上有所不同，sql中的if更像⼀个函数，⽽不是代表⼀个语句，但也有和编程语⾔中相差不⼤的条件语句，⽐如case。简单介绍⼀下3种在⼀般条件语句中的⽤法，不做更深的理解。不深究的原因可以看下例题思路解析部分。
IF( expr1 , True_expr , False_expr ) # expr1为真返回True_expr, 假则返回False_expr
IFNULL( expr1 , Null_expr ) # expr1的值不为空仍返回该值, 值为空则返回Null_expr
case when 条件1 then 结果1
when 条件2 then 结果2
else 结果N
end
例题简介
Leetcode 180：编写⼀个 SQL 查询，查所有⾄少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
思路解析
写代码写多了，遇到这种情况程序思维就觉得很简单，if+count的组合应该很容易就解决了。但是在sql中，⼀⽅⾯我对if,ifnull, case when等条件语句和函数不太了解，在帮助⽂档以及mysql应知应会这种⼯具书中都没有怎么提及，只能在csdn上搜索相关⽂档，另⼀⽅⾯，这类题⽬的逻辑并⾮⾮常复杂，我个⼈是不想为这种程度的逻辑去使⽤function或procedure。
⼀般的sql解法可能会对同⼀张表多次连接后加上较复杂的where⼦句，但我是有些不太习惯这种思维模式的，感觉过于暴⼒；另外⼀种解法是程序思维，使⽤⾃定义变量和条件语句来达到函数的效果。因此，我会列出两种⽅法，但对第⼆种⽅法更有倾向性。
⽅法1：同表多连接
⽆论如何，⽤的最多的可能还是相同表连接的查询⽅式来表⽰检索条件。
mysql> select distinct L1.Num as ConsecutiveNums
-> from Logs L1, Logs L2, Logs L3
-> where L1.Id = L2.Id - 1  # 条件语句
-> and L2.Id = L3.Id - 1
-> and L1.Num = L2.Num
-> and L2.Num = L3.Num;
+-----------------+
| ConsecutiveNums |
+-----------------+
|              1 |
+-----------------+
1 row in set (0.01 sec)
这⾥有⾄少有⼀处可以优化，就是在表连接出可以使⽤外连接减少检索的⾏数，速度的确快了，但思路上基本没有变化。
mysql> select distinct L1.Num as ConsecutiveNums
-> from Logs L1 left join Logs L2 on L1.Num = L2.Num
-> left join Logs L3 on L2.Num = L3.Num
-> where L1.Id = L2.Id - 1
-> and L2.Id = L3.Id - 1;
+-----------------+
| ConsecutiveNums |
+-----------------+
|              1 |
+-----------------+
1 row in set (0.00 sec)
⽅法2：⼦查询（⾃定义变量）
这种解法是我希望在sql中实现的，但在我原来的理解中，可能需要定义function or procudure，不过因为逻辑上⼜没那么复杂不想⿇烦所以⼀直觉得没必要。但这位⽼兄的解法给我提供了⼀个类函数的查询模板让我思考。
可能因为mysql的直接堆叠的查询逻辑更容易看懂，我看到Leetcode上数据库练习下的评论既少⼜表⽰对这种编程思维不太理解。其实包括我⾃⼰第⼀次看到的时候也有点懵。但看懂了以后会觉得⽐原来的⽅法更有趣也更快。
先把代码附上，接下来从⼦查询开始说明这种逻辑如何在sql中更好的实现。
SELECT DISTINCT a.Num ConsecutiveNums FROM (  # 主句
SELECT t.Num ,          # ⼦表1
@cnt:=IF(@pre=t.Num, @cnt+1, 1) cnt,
@pre:=t.Num pre
FROM Logs t, (SELECT @pre:=null, @cnt:=0) b) a  # ⼦表2
WHERE at >= 3
作者：hy3300
链接：leetcode-cn/problems/two-sum/solution/bu-shi-yong-idqing-kuang-xia-tong-ji-by-hy3300/
来源：⼒扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，⾮商业转载请注明出处。
⽅法2详解
⼦查询理解如果从主句开始，⽐较难⽤语⾔说明⼦查询返回的表的结构组成，所以我从⼦表2开始说明，并依次扩充直到主句
1. ⼦表2
select @pre:=null, @cnt:=0
⽣成包含两个字段的表，但这两个字段本⾝没有意义，其主要作⽤是通过建⽴表来初始化两个⾃定义
变量⽤于迭代，cnt变量⽤于存储当前Num出现的次数，pre⽤于存储当前的Num进⾏下⼀条数据⽐较。
2. ⼦表1
SELECT t.Num ,
@cnt:=IF(@pre=t.Num, @cnt+1, 1) cnt,
@pre:=t.Num pre
FROM Logs t, ⼦表2 b
+------+------+------+
| Num  | cnt  | pre  |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    2 |    1 |    2 |
|    1 |    1 |    1 |
|    2 |    1 |    2 |
|    2 |    2 |    2 |
+------+------+------+
将⼦表2替换命名为b，该句可以理解为从表Logs t中选取t.Num, cnt, pre 三个字段，依次返回三个值：Num，Num出现次数，保存本条数据中的Num值。因为没有where⼦句的限制，select会对每⾏的Num检索⼀次，并计算出相对应的cnt和pre值。这种结构类似于编程语⾔中的while-if，每⼀⾏相当于⼀次循环，cnt和pre变量会在每次循环中随条件⽽变化，同时这⾥如果需要控制次数可以使⽤LIMIT语句。
可以看到，⼦表1的效果就是函数封装的效果，相当有趣。
4.  SELECT DISTINCT a.Num ConsecutiveNums
FROM ⼦表1 a
WHERE at >= 3
当我们看到⼦表1的结果表之后，再来看主句就相当容易理解了，⽆外乎是⼀个简单的查询语句。
总结
以往我使⽤⼦查询，通常会将⼦查询作为计算字段来使⽤，⽐如select c1, (select c2 from t2) as c2 from t1 或 select * from t1 where t1.c1 = (select c2 from t2 where id=1)，⽽在本例中，⼦查询起到了类似过滤的作⽤，即利⽤变量重新构建了⼀个新表，这种⽅法⼤⼤增加了⼦查询的易⽤性和可⾏性，未来在遇到⽐⼀般查询稍复杂⼀些，但逻辑上⼜不会过于繁杂的情况，可以尝试在⼦查询中使⽤⾃定义函数和条件语句，构建⼀个具有函数效果的sql查询语句。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。