如何修改此查询而不增加返回的行数?

SELECT DISTINCT ID,
    CASE WHEN EXISTS (
            SELECT * FROM Table1 y 
            WHERE y.TYPE_VALUE = 0 AND ID = x.ID) 
        THEN 1 
        ELSE 0 END AS HAS_TYPE_0,
    CASE WHEN EXISTS (
            SELECT * FROM Table1 y
            WHERE y.TYPE_VALUE = 1 AND ID = x.ID) 
        THEN 1 
        ELSE 0 END AS HAS_TYPE_1
FROM Table1 x;

如果表中有大量的元素,那么它的性能就不会那么好了——这些嵌套的子选择在性能方面往往是致命一击。

SELECT 
     ID,
     CASE WHEN MIN(TYPE_VALUE) = 0 THEN '1' ELSE 0 END AS HAS_TYPE_0,
     CASE WHEN MAX(TYPE_VALUE) = 1 THEN '1' ELSE 0 END AS HAS_TYPE_1
FROM Table1
GROUP BY ID;

select distinct ID, TYPE_VALUE from OTHER_TABLE 使用

select ID,  
MAX(CASE WHEN TYPE_VALUE =0 THEN 1 END) as has_type_0,  
MAX(CASE WHEN TYPE_VALUE =1 THEN 1 END) as has_type_1  
from OTHER_TABLE
GROUP BY ID;

你也可以使用 PIVOT