表为单独表,树结构 layer共有4层, 此sql为通过id list 查询出 layer = 2 的 id 个数
id , parent_id, layer
SELECT
COUNT(DISTINCT ID) AS count
FROM
(
SELECT
DISTINCT parent_geography_id AS ID
FROM
core_master_db.geography
WHERE
(
geography_id IN (49887, 49888, 49886, 1775, 1)
OR
geography_id IN
(
SELECT
DISTINCT parent_geography_id
FROM
core_master_db.geography
WHERE
geography_id IN (49887, 49888, 49886, 1775, 1)
AND
layer = 4
)
)
AND layer = 3
UNION ALL
SELECT
DISTINCT geography_id AS ID
FROM
core_master_db.geography
WHERE
geography_id IN (49887, 49888, 49886, 1775, 1)
AND
layer = 2
) geography