mysql - 选择并计算结果在其他地方的位置

Question

我有一个如下表:

ID | Name              | Type        | State            | County
-------------------------------------------------------------------
1  | Rheinland-Pfalz   | State       | Rheinland-Pfalz  | NULL
2  | Trier             | City        | Rheinland-Pfalz  | NULL
3  | Alzey-Worms       | County      | Rheinland-Pfalz  | Alzey-Worms
4  | Alzey             | City        | Rheinland-Pfalz  | Alzey-Worms
5  | Worms             | City        | Rheinland-Pfalz  | Alzey-Worms
6  | Lorem             | County      | Rheinland-Pfalz  | Lorem
7  | Ipsum             | City        | Rheinland-Pfalz  | Lorem

现在我想要获取“Rheinland-Pfalz”州内的所有县及其包含的城市和所有县自由城市。

希望的结果:

ID | Name          | Type        | Included         
-------------------------------------------------------------------
2  | Trier         | City        | NULL  
3  | Alzey-Worms   | County      | 2  
6  | Lorem         | County      | 1

我的查询:

select a.id, a.name, a.type,
                (select count(*) from data where a.type="city" AND b.county=a.county) as included
                from data as a, data as b
                WHERE a.location_type='county' AND a.state = 'Rheinland-Pfalz' OR a.type='city' AND a.gmap_area1 = 'Rheinland-Pfalz' AND a.county IS NULL
                order by a.name asc

我的结果:

ID | Name          | Type        | Included         
-------------------------------------------------------------------
3  | Alzey-Worms   | County      | 0  
3  | Alzey-Worms   | County      | 0  
3  | Alzey-Worms   | County      | 0  
3  | Alzey-Worms   | County      | 0  
3  | Alzey-Worms   | County      | 0

...等等

Answer 1

不要羞于使用括号

SELECT 
    a.id, 
    a.name, 
    a.type, 
    (SELECT COUNT(*) FROM data 
        WHERE type='city' AND county=a.county) AS included 
FROM data AS a 
WHERE 
    a.state='Rheinland-Pfalz' AND 
    (a.type='county' OR (a.type='city' AND a.county IS NULL));