[MySQL][Syntax] COUNT中加条件 - Gukie/learning GitHub Wiki

refer

• http://www.mysqltutorial.org/mysql-count/

正题

其实count中是可以家条件的，不一定只是count某个列，最常见的就是 count(distinct column1)

如果想以 company_id进行分组，并且把已经删除掉的数据拿出来

SELECT *  FROM `user_history` 
WHERE 
  `user_id` in (SELECT `user_id`  FROM `user` WHERE `is_deleted` ='n') 
  and `company_id` REGEXP '^ROLE_[0-9]+' 
GROUP BY 
  `user_id` 
HAVING 
  COUNT(IF (`is_deleted`='n',1,null)) =0