我可以加入3张表有某种条件

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

---

想要改进此问题？更新该问题，因此它仅针对一个问题，仅编辑此帖子。

闭合 2年前。

Answer 1

SELECT
  p.name,
  COUNT(DISTINCT c.id) AS city_count
FROM
  residences   AS r
INNER JOIN
  cities       AS c
    ON c.id = r.city_id
INNER JOIN
  provinces    AS p
    ON p.id = r.province_id
WHERE
      r.status = 1
  AND c.status = 1
  AND p.status = 1
GROUP BY
  p.name

或者...

SELECT
  p.name,
  COUNT(*) AS city_count
FROM
  cities       AS c
INNER JOIN
  provinces    AS p
    ON p.id = c.province_id
WHERE
      c.status = 1
  AND p.status = 1
  AND EXISTS (
    SELECT *
      FROM residences
     WHERE status      = 1
       AND city_id     = c.id
       AND province_id = p.id
  )
GROUP BY
  p.name