对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-题目》