php - 在这种情况下，如何从具有不同 where 条件和不同 group by 条件的同一个表中进行选择？

Question

基本上这就是我想要实现的目标，但我不知道如何在单个查询中完成它，我不知道是否可以在单个查询中完成。

<强> SQL fiddle with example data

查询1:

SELECT 
    cityId, 
    COUNT(cityId) 
FROM 
    data 
WHERE 
    streetId IS NULL 
    AND houseId IS NULL 
GROUP BY cityId

查询2:

SELECT 
    cityId, 
    streetId, 
    COUNT(streetId) 
FROM 
    data 
WHERE 
    streetId IS NOT NULL 
    AND houseId IS NULL 
GROUP BY 
    streetId

查询3:

SELECT 
    cityId, 
    streetId, 
    houseId, 
    COUNT(houseId) 
FROM
    data 
WHERE 
    streetId IS NOT NULL 
    AND houseId IS NOT NULL 
GROUP BY 
    houseId

有没有办法在单个查询中做到这一点？

Answer 1

那么，您可以使用 UNION ALL 将 3 个查询的结果放在一起。这需要您调整查询，以便它们都返回相同的列。当原始查询中不可用时，我用 NULL 填充了附加列。

SELECT 
    cityId, 
    NULL street_id, 
    NULL houseId, 
    COUNT(cityId) cnt 
FROM
    data 
WHERE 
    streetId IS NULL 
    AND houseId IS NULL 
GROUP BY 
    cityId
UNION ALL SELECT 
    cityId, 
    streetId, 
    NULL, 
    COUNT(streetId) 
FROM 
    data 
WHERE 
    streetId IS NOT NULL 
    AND houseId IS NULL 
GROUP BY 
    streetId
UNION ALL SELECT 
    cityId, 
    streetId, 
    houseId, 
    COUNT(houseId) 
FROM
    data 
WHERE 
    streetId IS NOT NULL 
    AND houseId IS NOT NULL 
GROUP BY 
    houseId

<强> Results in your db fiddle :

cityId  street_id   houseId     cnt
-----------------------------------
1       (null)      (null)      2
2       (null)      (null)      1
2       2           (null)      1
3       3           (null)      5
6       4           3           2