mysql 一条语句多种count如何写

问题描述

现在的统计是这样写的

select column1 title,count(1) count1FROM table1WHERE column2 is not nullAND column2 > ’2016-06-13’AND column2 < ’2016-06-28’AND column3 is not nullgroup BY titlehaving count1 > 3000

现在要再加一列统计，数据是在这个基础上再加一个条件过滤，大概就是要下面这样的效果，要怎么写？

select column1 title,count(1) count1,count(column3=’abc’) count2FROM table1WHERE column2 is not nullAND column2 > ’2016-06-13’AND column2 < ’2016-06-28’AND column3 is not nullgroup BY titlehaving count1 > 3000

问题解答

select column1 title,count(1) count1, sum(IF(column3=’abc’, 1, 0)) count2FROM table1WHERE column2 > ’2016-06-13’AND column2 < ’2016-06-28’AND column3 is not nullgroup BY titlehaving count1 > 3000

试试看条件中的column2 is not null没用，可以去掉。

楼上正解，也可以试试这个，结果是一样的

select column1 title,count(1) count1,count(column3=’abc’ or null) count2FROM table1WHERE column2 between ’2016-06-14’ and ’2016-06-27’AND column3 is not nullgroup BY titlehaving count(1) > 3000 -- 用别名count1会报错的，这里不能用别名