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;

        统计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;

        再过来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;

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

        需要再做一次调整,比如两个第一后,累加的值就变成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;

        这样就符合实际的情况

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;

        由结果可以看出,对于小于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;

        然后再过滤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 查询实战1-题目

上一篇:人工智能数据分析Python常用库 04 matplotlib库


下一篇:百度OCR身份证识别C++离线SDKV3.0 C#对接