在Mysql中使用变量进行复杂的查询(一)

注意，此文章使用的Mysql变量均是用户变量

简介

首先看一个需求，有如下数据表：

mysql> select * from t1 order by area_id;
+---------+-----------+
| area_id | order_num |
+---------+-----------+
|       1 |        22 |
|       1 |        10 |
|       1 |        10 |
|       2 |        10 |
|       2 |        10 |
|       2 |        22 |
|       3 |        10 |
+---------+-----------+
7 rows in set (0.14 sec)

可以看到，这是一个很非常普通的数据表。

需求一

假设某一次运营需要统计每一个地区的销量量，让你出一个报表给他，那么方法如下：

方法一

统计每一个地区的总销量：
常用SQL如下：

mysql> select t1.area_id, sum(t1.order_num) from t1 group by area_id;
+---------+-------------------+
| area_id | sum(t1.order_num) |
+---------+-------------------+
|       1 | 42                |
|       2 | 42                |
|       3 | 10                |
+---------+-------------------+
3 rows in set (0.14 sec)

从图中可以看到，该SQL已经可以把每个地区的销售总量全部统计完毕了。但是有一天，需求又变了。

需求二：

现在其他的运营也过来了，说需要按照区域的Id做为优先级，依次统计每一个优先级地区的总销量。即如下：

1. 地区1的销量为42，由于没有地区Id为0的，所以地区1的销量是42。
2. 地区2的销量为42，但是由于地区2的优先级高于1，所以地区2的销量需要包含地区1的销量
3. 地区3的销量为42，但是由于地区3的优先级高于1和2，所以地区3的销量需要包含地区1的销量和地区2的销量

此时你会发现，虽然可以进行groupby，但是却无法统计之前的累加和，此时Mysql变量就可以使用了

方法一:

mysql> SELECT
	r1.area_id,
	@t_total := @t_total + r1.total AS 't_total' 
FROM
	( SELECT @t_total := 0 ) r,
	( SELECT t1.area_id, sum( t1.order_num ) AS 'total' FROM t1 GROUP BY area_id ) r1 order by r1.area_id;
+---------+---------+
| area_id | t_total |
+---------+---------+
|       1 |      42 |
|       2 |      84 |
|       3 |      94 |
+---------+---------+
3 rows in set (0.18 sec)

由于需求是需要高一级的地区需要统计所以低一级地区的销量，所以此时这个需求已经完成了。

此时查看SQL的效果，会发现，地区2的销量已经包含了地区1的，而地区3的销量已经包含了地区1和地区2的，所以该条SQL已经符合运营的需求了。

Mysql变量的初步使用

在上面的这条SQL中，可以看到出现了@t_total这个变量，同时也出现了:=操作符，这就是Mysql的变量。

需求三

现在运营发现，这样统计的话有点问题，需要进行调整下：

1. 地区1的销量为42，不能包含自身的销量，于是只能统计比1小的地区，所以地区1的销量是0。
2. 地区2的销量为42，不能包含自身的销量，于是只能统计比2小的地区，所以地区1的销量是42。
3. 地区2的销量为10，不能包含自身的销量，于是只能统计比3小的地区，所以地区3的销量是84。

方法一

mysql> SELECT
	r1.area_id,
	@t_total := @t_total + r1.total AS 't_total',
	@before_total := @t_total - r1.total AS 'before_total' 
FROM
	( SELECT @t_total := 0 ) r,
	( SELECT @before_total := 0 ) rr,
	( SELECT t1.area_id, sum( t1.order_num ) AS 'total' FROM t1 GROUP BY area_id ) r1;
+---------+---------+--------------+
| area_id | t_total | before_total |
+---------+---------+--------------+
|       1 |      42 |            0 |
|       2 |      84 |           42 |
|       3 |      94 |           84 |
+---------+---------+--------------+
3 rows in set (0.16 sec)

此时会看到before_total字段完美的符合和需求三。

Mysql变量

通过需求二和需求三可以看到Mysql的变量使用技巧，在Mysql中，使用变量可以实现很多复杂的需求。但是在使用Mysql变量的时候需要注意，用户变量需要配合Select一起来使用。