MySQL—常见业务查询案例

如何删除重复数据

删除评论表中对同一订单同一商品的重复评论，只保留最早的一条

1. 查看是否存在对同一订单同一商品的重复评论

   SELECT order_id,product_id,count(*) 
   FROM product_comment
   GROUP BY order_id,product_id HAVING count(*)>1  
   

2. 备份商品评论 product_comment 表

   # 创建备份表结构
   CREATE TABLE bak_product_comment＿20220107
   AS
   SELECT * FROM　product_comment
   # create table a as select * from b 可以创建一个与b表结构一样的表，但是在实际应用中最好不要这么创建表。原因是这样只创建表的结构，而不会将原表的默认值一起创建。不能将原表中的default value也一同迁移过来
   
   CREATE TABLE bak_product_comment＿20220107
   LIKE
   product_comment
   
   # 导入备份表数据
   IINSERT INTO bak_product_comment＿20220107
   SELECT * FROM　product_comment
   

3. 删除同一订单的重复评论

   DELETE a FROM product_comment a
   JOIN (
       SELECT order_id, product_id, MIN(comment_id) AS comment_id
       FROM product_comment
       GROUP BY order_id, product_id
       HAVING COUNT(*)>=2
   ) b ON a.order_id=b.order_id AND a.product_id=b.product_id
   AND a.comment_id > b.comment_id
   

如何进行分区间统计

统计消费总金额大于 1000 元的，800 到 1000 元的，500 到 800 元的，以及 500 元以下的人数

SELECT 
    COUNT(CASE WHEN IFNULL(total_money,0) >= 1000 THEN a.customer_id END) AS '大于1000',
    COUNT(CASE WHEN IFNULL(total_money,0) >= 800 AND IFNULL(total_money,0) < 1000 THEN a.customer_id END) AS '800~1000',
    COUNT(CASE WHEN IFNULL(total_money,0) < 500 THEN a.customer_id END) AS '小于500'
FROM
    customer_user AS a
LEFT JOIN (
    SELECT customer_id, SUM(order_monery) AS total_money
    FROM order_master 
    GROUP BY customer_id
) AS b ON a.customer_id=b.customer_id