在转换日期格式时的php mysql案例

Question

Here is the normal mysql query

SELECT SUM( Minutes ) as minsDecPrac, COUNT( DISTINCT (CAST(dtDateTime as DATE))) as playDecPrac FROM `tbl_atschool_timelog` WHERE `intuid` = 48876 AND `intGametypeId` = 2 AND `intGame` = 1 

Here is the Output

minsDecPrac     playDecPrac   
17                 1

Here is the query using CASE WHEN

SELECT SUM(CASE WHEN intGametypeId = 2 AND intGame =1 THEN Minutes ELSE 0 END) AS minsDecPrac COUNT(DISTINCT CASE WHEN intGametypeId = 2 AND intGame =1 THEN  (CAST(dtDateTime as DATE)) ELSE 0 END) AS playDecPrac FROM `tbl_atschool_timelog` WHERE `intuid` = 48876

Here is the Output

minsDecPrac     playDecPrac   
17                 2

The problem is that if i use CASE WHEN, the output of "playDecPrac"(dtDateTime ) is wrong. I don't know what i am wrong. Please help me if anybody knows

Answer 1

This clause here:

COUNT(DISTINCT CASE WHEN intGametypeId = 2 AND intGame =1 THEN  (CAST(dtDateTime as DATE)) ELSE 0 END)

counts the 0 from ELSE 0 as a distinct value, in addition to the dates. So the COUNT(DISTINCT ) is one higher than it should be.

You might try ELSE NULL.