记录sql语句

#同一订单同一商品评论大于1

select order_id,product_id,count(*) as comment_count from product_comment group by order_id,product_id having count(*)>1

#删除重复订单商品评论

delete a from product_comment a
join(
    select order_id,product_id,min(id) as 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.id > b.id

#区间统计

select count(case when ifnull(total_money,0) >= 10 then a.id end) as '大于10',
             count(case when ifnull(total_money,0) >= 5 and ifnull(total_money,0) < 10 then a.id end) as '5-10',
             count(case when ifnull(total_money,0) >= 0 and ifnull(total_money,0) < 5 then a.id end) as '小于5'
from customer a
left join
(
    select customer_id,sum(price) as total_money
    from order_master
    group by customer_id
)b on a.id=b.customer_id