周休二日與平日旅遊(舉例)在MySQL上一段SQL 語句Count的運算

 

不錯的思路
觸發我在想怎麼運用在其他的資料庫
應該是case when吧~~

The IF() expressions determine, for each column value, whether it should be counted.
If so, the expression evaluates to 1 and COUNT() counts it. If not, the expression evaluates
to NULL and COUNT() ignores it. The effect is to count the number of values that satisfy
the condition given as the first argument to IF().

類似的邏輯碰到是否有品項資料未key in而造成NULL值
不過坦白說, 只要做一次group by,就可以看到null的count數了
坦白說我沒寫過底下的邏輯應用
寫出來只是為了我將來參考用
就只為了把null加到count(distinct)裏頭
不就直接加1就好了.....
COUNT(DISTINCT) ignores NULL values. To count NULL as one of the values in the set if
it’s present, use one of the following expressions:
COUNT(DISTINCT val) + IF(COUNT(IF(val IS NULL,1,NULL))=0,0,1)
COUNT(DISTINCT val) + IF(SUM(ISNULL(val))=0,0,1)
COUNT(DISTINCT val) + (SUM(ISNULL(val))<>0)

Posted in: count distinct,count null,weekday,weekend