mysql group by 范围_MySQL 按照范围/等级 进行Group By
我們要做一張報(bào)表表格，從mysql里取出數(shù)據(jù)，然后按等級(jí)分組，如何實(shí)現(xiàn)呢？
MySQL 示例 SQL:
SELECT
ftime,
sum(cost) as cost,
count(advertiser_id) as aduser_num,
sum(pv) as pv,
sum(vc) as vc,
CASE
WHEN cost>100000 THEN ‘1’
WHEN cost>50000 and cost<=100000 THEN ‘2’
WHEN cost>10000 and cost<=50000 THEN ‘3’
WHEN cost>5000 and cost<=10000 THEN ‘4’
WHEN cost>1000 and cost<=5000 THEN ‘5’
WHEN cost>100 and cost<=1000 THEN ‘6’
ELSE ‘7’ END as cost_range
from (
SELECT
ftime,
advertiser_id,
sum(real_cash+vir_cash+gift_cash+divide_cash)/100 as cost,
sum(exposure_cnt) as pv,
sum(valid_click_cnt) as vc
from etail_day
where ftime in(20140603) group by ftime,advertiser_id
) as tmp_table
group by
ftime,CASE
WHEN cost>100000 THEN ‘1’
WHEN cost>50000 and cost<=100 THEN ‘2’
WHEN cost>10000 and cost<=50000 THEN ‘3’
WHEN cost>5000 and cost<=10000 THEN ‘4’
WHEN cost>1000 and cost<=5000 THEN ‘5’
WHEN cost>100 and cost<=1000 THEN ‘6’
ELSE ‘7’ END ;
利用SQL對(duì)某個(gè)表中的數(shù)據(jù)按照某個(gè)范圍進(jìn)行等級(jí)劃分，并按照劃分后的等級(jí) group by。實(shí)現(xiàn)思路見上：
(1)將該表的記錄取出，如果有函數(shù)運(yùn)算的話，搞個(gè)虛擬字段表示
(2)將(1)中的查詢結(jié)果構(gòu)建一張臨時(shí)表，然后弄一個(gè)臨時(shí)表別名：tmp_table
(3)從臨時(shí)表中取出數(shù)據(jù)，按范圍group by
可能會(huì)遇到的問題：
MySQL Error: Every derived table must have its own alias(這句話的意思是說每個(gè)派生出來的表都必須有一個(gè)自己的別名)，那可能是別名不對(duì)，檢查下臨時(shí)表別名
可能會(huì)出現(xiàn)null的值，這時(shí)候
?null和任何值都不能比較
?null只能用is null 或者is not null 來判斷，不能用=或者！=來判斷
總結(jié)
以上是生活随笔為你收集整理的mysql group by 范围_MySQL 按照范围/等级 进行Group By的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql怎么访问用户B_MySQL访问
- 下一篇： 女生什么时候减肥效果最快最好