最近工作繁忙,没时间写技术博客,简单发点写过的主要sql吧
同字段不同值分列统计
其实就是利用了一下mysql的case... then...做聚合
SELECT
t.ads_id,
t.position_id,
a. NAME AS ads_name,
SUM(CASE t.action WHEN 0 THEN 1 ELSE 0 END) 'require',
SUM(CASE t.action WHEN 1 THEN 1 ELSE 0 END) 'in',
SUM(CASE t.action WHEN 2 THEN 1 ELSE 0 END) 'click',
SUM(CASE t.action WHEN 4 THEN 1 ELSE 0 END) 'inPage',
SUM(CASE t.action WHEN 8 THEN 1 ELSE 0 END) 'out'
FROM
`statistics_ads` `t`
LEFT JOIN `ads` `a` ON `t`.`ads_id` = `a`.`id`
GROUP BY
`t`.`ads_id`,
`t`.`position_id`
ORDER BY
`t`.`ads_id` DESC;
效果