MySQL COUNT(CASE WHEN ... THEN DISTINCT 列)

Question

我的查询:

COUNT(
    CASE WHEN YEAR(FieldValue) = YEAR(CURDATE()) 
    AND MONTH(FieldValue) = MONTH(CURDATE())
    THEN 1
    END
) AS mtd

我想要这样的东西:

COUNT(
    CASE WHEN YEAR(FieldValue) = YEAR(CURDATE()) 
    AND MONTH(FieldValue) = MONTH(CURDATE())
    THEN DISTINCT ColumnID
    END
) AS mtd

这给出了一个错误。我不能使用 GROUP BY。如果我在 CASE 之前添加 DISTINCT 它不起作用，或者它将 FieldValue 列视为 DISTINCT 但我希望它将 ColumnID 计为不同。

请帮忙

Answer 1

试试这个

  COUNT(DISTINCT(
    CASE WHEN YEAR(FieldValue) = YEAR(CURDATE()) 
    AND MONTH(FieldValue) = MONTH(CURDATE())
    THEN ColumnID 
    END )

  ) AS mtd