mysql 几个带入场景的SQL语句

计算audit_status和product_id列的区别度，区分度越大的列，创建索引时放在左面

select count(distinct audit_status)/count(*) as audit_rate,count(distinct product_id)/count(*) as product_rate from product_comment;

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

    步骤一：查询是否存在对于同一订单同一商品的重复评论，    

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

    步骤二：备份product_comment表中

-- 创建一个与product_comment结构相同的表
create table bak_comment_161022 like product_comment;
-- 将表product_comment一份到bak_comment_161022表
insert into bak_comment_161022 select * from product_comment;

    步骤三：删除同一订单的重复评论

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 AND IFNULL ( total_money, 0 ) < 800 THEN a.customer_id END ) AS '500~800',
	 COUNT ( CASE WHEN ifnul ( total_money, 0 ) < 500 THEN a.customer_id END ) AS '小于500'       
FROM
	mc_customerdb.` customer_login ` a      
LEFT JOIN (SELECT customer_id, SUM ( order_money ) AS total_money FROM mc_orderdb.`order_master` GROUP BY customer_id) b 
ON a.customer_id = b.customer_id;

统计3月份之后的一个月中状态为1的数据id

SELECT
	m.ID,
	count(i.Status )
FROM
	VALUE_VIEW_METER_INFO m  INNER JOIN VALUE_METER_INSTANT i on i.EQID=m.ID
WHERE
	m.ybstatus= 0 
	AND i.EQTime >= '2020-03-03 00:00:00' 
	AND i.Status= 1 
GROUP BY
	m.ID
HAVING
	COUNT ( i.Status ) >= 700