mysql - 列出两个表的对应数据

Question

我是 sql 的新手，我有一个我无法真正向 google 描述的问题

SELECT name AS state 
FROM state 
WHERE id IN 
(
  SELECT state_id 
  FROM city 
  WHERE id=(SELECT city_id FROM zipcode)
)
;

这列出了我数据库中所有邮政编码的状态，我还想在与状态对应的附加列中列出邮政编码 (zipcode.zipcode)

非常感谢您的帮助

我的表格是这样的:

city

+----+----------+-----------+---------------------+---------+---------+
| id | state_id | county_id | name                | lat     | lng     |
+----+----------+-----------+---------------------+---------+---------+
|  1 |        1 |         1 | Prem, Oberbayern    | 47.6833 |    10.8 |
|  2 |        2 |         2 | Pfullendorf (Baden) | 47.9249 | 9.25718 |
|  3 |        3 |         3 | Wissen, Sieg        | 50.7833 |    7.75 |
|  4 |        1 |         4 | Miltenberg          | 49.7039 | 9.26444 |
|  5 |        1 |         5 | Moosthenning        | 48.6833 |    12.5 |
|  6 |        1 |         1 | Bernbeuren          | 47.7333 | 10.7833 |
|  7 |        4 |         6 | Demmin, Hansestadt  |    53.9 | 13.0333 |
|  8 |        2 |         7 | Konstanz, Universit | 47.6667 | 9.18333 |
|  9 |        5 |         8 | Teutschenthal       |   51.45 |    11.8 |
| 10 |        6 |         9 | Vierlinden          |  52.515 | 14.3141 |
+----+----------+-----------+---------------------+---------+---------+



zipcode

+----+---------+-------------+---------+
| id | city_id | district_id | zipcode |
+----+---------+-------------+---------+
|  1 |       1 |        NULL | 86984   |
|  2 |       2 |        NULL | 88630   |
|  3 |       3 |        NULL | 57537   |
|  4 |       4 |        NULL | 63897   |
|  5 |    4312 |         502 | 84164   |
|  6 |       6 |        NULL | 86975   |
|  7 |       7 |         778 | 17109   |
|  8 |       8 |        NULL | 78462   |
|  9 |       8 |        NULL | 78464   |
| 10 |       8 |        NULL | 78465   |
+----+---------+-------------+---------+



state

+----+------------------------+
| id | name                   |
+----+------------------------+
|  1 | Bayern                 |
|  2 | Baden-W?rttemberg      |
|  3 | Rheinland-Pfalz        |
|  4 | Mecklenburg-Vorpommern |
|  5 | Sachsen-Anhalt         |
|  6 | Brandenburg            |
|  7 | Niedersachsen          |
|  8 | Schleswig-Holstein     |
|  9 | Nordrhein-Westfalen    |
| 10 | Th?ringen              |
+----+------------------------+

Answer 1

使用明确的JOIN:

SELECT z.*, s.name AS state
FROM zipcode z JOIN
     city c
     ON z.city_id = c.id JOIN
     state s
     ON c.state_id = s.id;

如果您正在学习 SQL，请专注于学习 JOIN 而不是 IN。