問題描述

現(xiàn)在的統(tǒng)計(jì)是這樣寫的

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

現(xiàn)在要再加一列統(tǒng)計(jì)，數(shù)據(jù)是在這個(gè)基礎(chǔ)上再加一個(gè)條件過濾，大概就是要下面這樣的效果，要怎么寫？

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沒用，可以去掉。

樓上正解，也可以試試這個(gè)，結(jié)果是一樣的

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會(huì)報(bào)錯(cuò)的，這里不能用別名