mysql 中sum (if()) 用法:
举例一:
原表:
id fenlei date
1 分类1 20130316
2 分类2 20130316
3 分类3 20130317
4 分类2 20130317
5 分类3 20130318
需要查上表,得到结果插入新表,新表结构如下:
id fenlei_1 fenlei_2 fenlei_3 date
1 1 1 0 20130316
2 0 1 1 20130317
3 0 0 1 20130318
即要得到每个分类在每天各自消息数
select id,sum(if(fenlei='分类1',1,0 )) as fenlei_1, sum(if(fenlei='分类2',1,0 )) as fenlei_2, sum(if(fenlei='分类3',1,0 )) as fenlei_3, date
from 原表
group by date
(意思是如果fenlei='分类1',则 将1累加到fenlei_1上,否则将0累加到fenlei_1,即按日期统计分类1的有几条 )
举例二:
select sum(qty) as total_qty
from 表名
group by product_id
若qty有负值,则sum(qty)统计就会出现问题,此时用sum(if())就可以了。 SQL为:
select sum(if(qty > 0, qty, 0)) as total_qty (意思是如果qty > 0, 将qty的值累加到total_qty, 否则将0累加到total_qty.)
from 表名
group by product_id
(sum( if( qty < 0, 1, 0 )) as negative_qty_count 统计负值记录有多少条)
举例三:
积分榜:有两种礼物,礼物id=10312是1积分,礼物id=10316是10积分,按人统计积分并排榜,sql如下:
select sum( if(giftid = 10312, giftnum, giftnum * 10)) as total
from 表名
group by 用户id
注意:若使用Codeigniter框架,sum(if())报错问题
$this->gift_db->select('`send_uid` as `uid`, sum( if(giftid = 10312, `giftnum`, `giftnum` * 10)) as `total`')
->from('hd_send_gift_log_hotlive')
->where_in('giftid', array(10312, 10316))
->where('dt >=', $bdate)
->where('dt <=', $edate)
->where('partner_id', APP_PARTNER_ID)
->group_by('uid')
->order_by('total', 'DESC');
当执行上面代码时会出错,用last_query()打印出执行的sql语句,如下:
SELECT `send_uid` as `uid`, sum(if(giftid = 10312, `giftnum`, `giftnum*10))` as `total`
FROM `hd_send_gift_log_hotlive`
WHERE giftid IN(10312, 10316)
AND `dt` >= '2017-03-28 10:00:00' AND `dt` <= '2017-05-31 00:00:00'
AND `partner_id` = 126
GROUP BY `uid`
ORDER BY `total` DESC
如上可以看到:`giftnum*10))`,引号的位置有误,正确应该是`giftnum` *10))
解决办法:
$this->db->select('your Query', FALSE);
不加FALSE的效果:
SELECT `column1`, `column2` FROM table
加上FALSE的效果:
SELECT column1, column2 FROM table
即加上FALSE,则select里的字段在执行时不会在添加引号,即解决了上面CI框架使用sum(if())引号位置出错的问题