? ? ? ? 對(duì)mysql 查詢實(shí)戰(zhàn)-變量方式-題目，進(jìn)行一個(gè)解答。（先看題，先做，再看解答）

1、查詢表中?少連續(xù)三次的數(shù)字

1，處理思路

????????要計(jì)算連續(xù)出現(xiàn)的數(shù)字，加個(gè)前置變量，記錄上一個(gè)的值，跟當(dāng)前值做比較，如果相同，則進(jìn)行累加

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

????????統(tǒng)計(jì)count_num大于3的數(shù)字就好了。

2，進(jìn)行統(tǒng)計(jì)

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，處理思路

????????要比較昨日上升的，要加個(gè)變量，記錄昨天的值。用今天的值，跟昨天的值，做一個(gè)比較，并做一個(gè)標(biāo)記。

 -- 根據(jù)時(shí)間排序，現(xiàn)在的值(amount)，比昨天的值（@pre）大的 flag設(shè)置為為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;

? ? ? ? 再過(guò)來(lái)flag=1的數(shù)據(jù)

2，進(jìn)行過(guò)濾統(tǒng)計(jì)

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、查詢投票結(jié)果的排名情況

????????即第一名、第二名是誰(shuí)，或者理解為，按倒序排好后，加個(gè)序號(hào)（區(qū)別就是同值的，排名是一樣的）。

1，處理思路

????????排名，也是要跟前一個(gè)值做比較，值一樣，排名不變；更小，就加1

-- 與前面一行進(jìn)行比較，值一樣，排名不變；更小，就加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;

????????這個(gè)排名看起來(lái)好奇怪，不符合實(shí)際的。兩個(gè)第二名，下一個(gè)就是第四名了。

????????需要再做一次調(diào)整，比如兩個(gè)第一后，累加的值就變成2了。因此需要再聲明一個(gè)變量，用于判斷累加的次數(shù)。

2，實(shí)際情況

-- 先累加相同的值， 然后把值賦值給另外一個(gè)變量。 再進(jìn)行二次判斷，如果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，進(jìn)行提取

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;

????????這樣就符合實(shí)際的情況

4、查詢?站訪問(wèn)?峰期

????????目標(biāo)： 查詢網(wǎng)站訪問(wèn)高峰時(shí)期，高峰時(shí)期定義：至少連續(xù)三天訪問(wèn)量>=1000

1，先統(tǒng)計(jì)訪問(wèn)量大于1000

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

2，用倒序排列，統(tǒng)計(jì)數(shù)量大于3的

再聲明一個(gè)變量，用于記錄上一條flag值

--連續(xù) 用倒序排列， 再聲明一個(gè)變量，用于記錄上一條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;

????????由結(jié)果可以看出，對(duì)于小于3的，判斷前面一條的flag是否為1，即@pre的值為1

3，對(duì)于小于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_numFROM visit_summary
) AS tmp ORDER BY id DESC;

? ? ? ? 然后再過(guò)濾flag值為1的，是符合要求的。

4，進(jìn)行過(guò)濾

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;

總結(jié)：

????????上面這些題目，都有進(jìn)行比較的特點(diǎn)，連續(xù)出現(xiàn)多少次，或者跟昨天比有上升，還是排名的情況，都要進(jìn)行比較，尤其是連續(xù)出現(xiàn)多次，排名這種情況，用變量寫(xiě)反而比直接寫(xiě)sql要方便很多。后續(xù)有出現(xiàn)要比較的情況，就可以考慮用變量的方式去處理，多嘗試！！！

????????

? ? ? ? 上一篇： 《mysql 查詢實(shí)戰(zhàn)-變量方式-題目》

? ? ? ? 下一篇： 《mysql 查詢實(shí)戰(zhàn)1-題目》