返回首页
当前位置: 主页 > 数据库 > Mysql教程 >

MySQL 按照范围/等级 进行Group By

时间:2016-04-28 12:56来源:电脑教程学习网 www.etwiki.cn 编辑:admin

我们要做一张报表表格,从mysql里取出数据,然后按等级分组,如何实现呢?

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对某个表中的数据按照某个范围进行等级划分,并按照划分后的等级 group by。实现思路见上:

(1)将该表的记录取出,如果有函数运算的话,搞个虚拟字段表示
(2)将(1)中的查询结果构建一张临时表,然后弄一个临时表别名:tmp_table
(3)从临时表中取出数据,按范围group by

可能会遇到的问题:

MySQL Error: Every derived table must have its own alias(这句话的意思是说每个派生出来的表都必须有一个自己的别名),那可能是别名不对,检查下临时表别名

可能会出现null的值,这时候


◦null和任何值都不能比较

 

◦null只能用is null 或者is not null 来判断,不能用=或者!=来判断

 

------分隔线----------------------------
标签(Tag):mysql 数据库 mysql数据库
------分隔线----------------------------
推荐内容
猜你感兴趣