f
周休二日與平日旅遊(舉例)在MySQL上一段SQL 語句Count的運算 ~ 迪貝之家
skip to main |
skip to sidebar
不錯的思路 |
觸發我在想怎麼運用在其他的資料庫 |
應該是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)
|