使用记录

分组统计

根据不同条件对同一字段进行分组统计：

select
  *,
  SUM(IF(operation = 'add', 1, 0)) AS add_count,
  SUM(IF(operation = 'delete', 1, 0)) AS delete_count
from 
  song_analytics 
where 
  operation = 'add' 
or 
  operation = 'delete'
group by
  ident_id

替换字段中的字符串

// a.com 替换成 b.com
UPDATE luck_product_image SET image = replace(image, 'a.com', 'b.com');

重复数据

通过 group by 对多个字段进行分组：

select *, count(*) as count from song_product_to_category group by product_id, category_id HAVING count > 1;

删除多个字段重复的数据，只保留一条：

DELETE FROM 表名 WHERE (字段1,字段2,字段3)
IN 
(SELECT 字段1,字段2,字段3 FROM (SELECT 字段1,字段2,字段3 FROM 表名 GROUP BY 字段1,字段2,字段3 HAVING COUNT(*)>1) s1) 
AND
id NOT IN (SELECT id FROM (SELECT id FROM 表名 GROUP BY 字段1,字段2,字段3 HAVING COUNT(*)>1) s2);
// 示例
DELETE FROM song_product_to_category WHERE (product_id, category_id)
IN 
(SELECT product_id, category_id FROM (SELECT product_id, category_id FROM song_product_to_category GROUP BY product_id, category_id HAVING COUNT(*)>1) s1) 
AND
id NOT IN (SELECT id FROM (SELECT id FROM song_product_to_category GROUP BY product_id, category_id HAVING COUNT(*)>1) s2);

更新评价表中用户名字

简单描述以下两种类型：

• 导入数据：customer_id为空，customer_name不为空；
• 客户提交数据：customer_id不为空，customer_name为空；

需求：批量更新评价表中customer_name为空的值。

select * from song_comment where customer_id != 0;
update song_comment set customer_name = (SELECT CONCAT(firstname, ' ', lastname) from song_customer where id = song_comment.customer_id ) where song_comment.customer_id != 0 and song_comment.customer_name = '';

Group By 分组 排序

查询搜索关键字，按搜索次数倒序排序：

select keyword, count(*) as total from song_search group by keyword ORDER BY total desc;