以嵌套子查询作为判断条件的单元SQL查询

Question

作为标题，我的查询如下：

SELECT
COUNT(
    DISTINCT (CASE WHEN id IN (
        SELECT DISTINCT id
        FROM some_table
        WHERE year = 2019
        AND month IN (1, 2, 3)
    ) tmp AND (*OTHERS CONDITIONS*) THEN id END)
) AS devices
FROM some_table
WHERE year = 2019
AND month IN (1, 2, 3);

但是，我一直收到这个错误：

cannot recognize input near 'SELECT' 'DISTINCT' 'id' in expression specification

我该怎么解决呢？

Answer 1

您不能像这样使用子查询。将子查询更改为联接，并在下列情况下使用它。

SELECT
COUNT(
    DISTINCT (CASE WHEN tmpid is not null 
         AND (*OTHERS CONDITIONS*) THEN id END)
) AS devices
FROM some_table
left join (SELECT DISTINCT id as tmpid
        FROM some_table
        WHERE year = 2019
        AND month IN (1, 2, 3)
    ) tmp on tmp.tmpid = id
WHERE year = 2019
AND month IN (1, 2, 3);