mysql 查询实战-变量方式-解答

àì夳堔傛蜴生んèń 2024-04-20 22:21 69阅读 0赞

对[mysql 查询实战-变量方式-题目][mysql _-_-]，进行一个解答。（先看题，先做，再看解答）

### 1、查询表中⾄少连续三次的数字 ###

#### 1，处理思路 ####

要计算连续出现的数字，加个前置变量，记录上一个的值，跟当前值做比较，如果相同，则进行累加

SELECT *, @count:=IF(@pre=number,@count+1,1) AS count_num,
     @pre:=number AS pre 
    FROM numbers,(SELECT@count:=0, @pre:=NULL) v;

![1210be2a47004a649018549d0131883f.png][]

统计count\_num大于3的数字就好了。

#### 2，进行统计 ####

SELECT DISTINCT number FROM(
    SELECT *,@count:=IF(@pre=number,@count+1,1) AS COUNT,@pre:=number AS pre FROM
    numbers, (SELECT @count:=0,@pre:=NULL) v
    ) AS tmp WHERE tmp.count >= 3;

###  ###

### 2、查询销售额较昨⽇上升的记录 ###

#### 1，处理思路 ####

要比较昨日上升的，要加个变量，记录昨天的值。用今天的值，跟昨天的值，做一个比较，并做一个标记。

-- 根据时间排序，现在的值(amount)，比昨天的值（@pre）大的 flag设置为为1，否则为0
    select *, @flag:=if(ammount>@pre,1,0) as flag, 
    @pre:=ammount as pre 
    from (select @flag:=0,@pre:=null) as v, sale order by record_date;

![6a5060711a2045b2bf41ea4340b4044b.png][]

再过来flag=1的数据

#### 2，进行过滤统计 ####

select id,record_date,ammount from
    (
    select *,@flag:=if(ammount>@pre,1,0) as flag,@pre:=ammount as pre from (select
    @flag:=0,@pre:=null) as v, sale order by record_date
    ) as tmp where flag=1

### 3、查询投票结果的排名情况 ###

即第一名、第二名是谁，或者理解为，按倒序排好后，加个序号（区别就是同值的，排名是一样的）。

#### 1，处理思路 ####

排名，也是要跟前一个值做比较，值一样，排名不变；更小，就加1

-- 与前面一行进行比较，值一样，排名不变；更小，就加1
    select name,votes,ranking from(
    select *,@ranking:=if(votes=@pre,@ranking,@ranking+1) as ranking,
    @pre:=votes as pre
    from (select @ranking:=0,@pre:=null
    ) as v,vote order by votes desc
    ) as tmp;

![00dc9e76f3a243bb96ca08e9bda76870.png][]

这个排名看起来好奇怪，不符合实际的。两个第二名，下一个就是第四名了。

需要再做一次调整，比如两个第一后，累加的值就变成2了。因此需要再声明一个变量，用于判断累加的次数。

#### 2，实际情况 ####

-- 先累加相同的值， 然后把值赋值给另外一个变量。 再进行二次判断，如果sumSame的值大于0，就用sumSame。
    SELECT *, @same:=IF(votes=@pre, @same+1,0) AS same,  
     @ranking:=IF(votes=@pre, @ranking, 
    @ranking+1+IF(@sumSame > 0, @sumSame,0)) AS ranking,
     @pre:=votes AS pre, @sumSame:=@same AS sunSame
    FROM (SELECT @ranking:=0,@same:=0,@pre:=NULL,@sumSame:=NULL
    ) AS v,vote ORDER BY votes DESC;

#### 3，进行提取 ####

SELECT id, NAME, votes, ranking  FROM (
    SELECT *, @same:=IF(votes=@pre, @same+1,0) AS same,  
     @ranking:=IF(votes=@pre, @ranking, @ranking+1+IF(@sumSame > 0, @sumSame,0)) AS ranking,
     @pre:=votes AS pre, @sumSame:=@same AS sunSame
    FROM (SELECT @ranking:=0,@same:=0,@pre:=NULL,@sumSame:=NULL
    ) AS v,vote ORDER BY votes DESC
    ) AS temp;

#### ![b9baa29bdb9641228dcfe6bbefb8a569.png][] ####

这样就符合实际的情况

### 4、查询⽹站访问⾼峰期 ###

目标： 查询网站访问高峰时期，高峰时期定义：至少连续三天访问量>=1000

#### 1，先统计访问量大于1000 ####

select *,@count:=if(visit_sum>=1000,@count+1,0) as count
    from visit_summary;

#### 2，用倒序排列，统计数量大于3的 ####

再声明一个变量，用于记录上一条flag值

--连续 用倒序排列， 再声明一个变量，用于记录上一条flag值。
     SELECT *, @pre:=@flag, @flag:=IF((count_num>=3) AND count_num>0,1,0) AS flag
    FROM(
    SELECT *, @count:=IF(visit_sum>=1000, @count+1,0) AS count_num 
    FROM visit_summary
    ) AS tmp1 ORDER BY id DESC;

![e46292ea7dae4fafa6c017c61ad4431c.png][]

由结果可以看出，对于小于3的，判断前面一条的flag是否为1，即@pre的值为1

#### 3，对于小于3的，判断前面一条的flag是否为1 ####

-- 小于3的，判断前面一条的flag是否为1。 
    SELECT *, @pre:=@flag,
    @flag:=IF((count_num>=3 OR @pre=1) AND count_num>0,1,0) AS flag
    FROM(
    SELECT *,@count:=IF(visit_sum>=1000,@count+1,0) AS count_num
     FROM visit_summary
    ) AS tmp ORDER BY id DESC;

![27a8ec5d11874beab57ccbad9e58db4b.png][]

然后再过滤flag值为1的，是符合要求的。

#### 4，进行过滤 ####

select * from(
    select *,@flag:=if((count>=3 or @flag=1) and count>0,1,0) as flag
    from(
      select *,@count:=if(visit_sum>=1000,@count+1,0) as count 
      from visit_summary
    ) as tmp order by id desc
    ) as tmp where tmp.flag=1 order by tmp.id;

#### 总结： ####

上面这些题目，都有进行比较的特点，连续出现多少次，或者跟昨天比有上升，还是排名的情况，都要进行比较，尤其是连续出现多次，排名这种情况，用变量写反而比直接写sql要方便很多。后续有出现要比较的情况，就可以考虑用变量的方式去处理，多尝试！！！

上一篇： 《[mysql 查询实战-变量方式-题目][mysql _-_-]》

下一篇： 《[mysql 查询实战1-题目][mysql _1-]》

[mysql _-_-]: https://blog.csdn.net/qq_35461948/article/details/137522525
[1210be2a47004a649018549d0131883f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/2e50d8634fde40768f4902a1d63e3a68.png
[6a5060711a2045b2bf41ea4340b4044b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/57e1fba35cb64034a31b0f3f9e6f01a0.png
[00dc9e76f3a243bb96ca08e9bda76870.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/7890950dd70943e7950cbc646c4ea00c.png
[b9baa29bdb9641228dcfe6bbefb8a569.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/47453fb135134bb1b44ede201a5d32f1.png
[e46292ea7dae4fafa6c017c61ad4431c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/488b4d7753464ce59737206eafcb1cb0.png
[27a8ec5d11874beab57ccbad9e58db4b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/9051ae7ff2ce4884ad7b8ab7bdb32616.png
[mysql _1-]: https://blog.csdn.net/qq_35461948/article/details/137565482?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22137565482%22%2C%22source%22%3A%22qq_35461948%22%7D