时间戳

首页 > 解决方案 > 如何在仅返回一次州和城市名称的同时返回与州和城市关联的每条记录？(MySql)

mysql - 如何在仅返回一次州和城市名称的同时返回与州和城市关联的每条记录？(MySql)

问题描述

我不太确定如何问这个问题，而且我已经搜索引擎一段时间了，还没有想出任何有用的东西。

假设我有以下三个表People，Cities并且States：

PeopleID |   Name   |    Age   | CityIDFK
--------------------------------------------
     1   |   John   |    24    |      20 
     2   |   Jim    |    28    |      21 
     3   |   Joan   |    49    |      10 
     4   |   Mike   |    37    |      10 
     5   |   Bruce  |    26    |      2 
     6   |   Peter  |    22    |      20 
     7   |   Oprah  |    27    |      3 
     7   |   Jake   |    21    |      1 

CityIDPK |    City     |   StateIDFK
---------------------------------------
     1     |  Seattle    |      1 
     2     |  Gotham     |      2 
     3     |  Oakland    |      4 
     10    |  Boise      |      5 
     20    |  Austin     |      6 
     21    |  Tyler      |      6

StateIDPK    |   StateName
----------------------------
      1      |   Washington
      2      |   New York
      3      |   Oregon
      4      |   California
      5      |   Idaho
      6      |   Texas

我怎样才能实现以下输出：

    StateName |   City     |    Name    
---------------------------------------
California    |   Oakland  |    Oprah  
Idaho         |   Boise    |    Mike  
              |            |    Joan
New York      |   Gotham   |    Bruce
Washington    |   Seattle  |    Jake
Texas         |   Austin   |    John
              |            |    Peter
              |   Tyler    |    Jim

我不确定上述输出是否可能，我可能会做这样的事情：

SELECT StateName, City, Name FROM People
INNER JOIN Cities
ON Cities.CityIDPK = People.CityIDFK
INNER JOIN States
ON States.StateIDPK = Cities.StateIDFK

但这将为每个人返回StateNameand City，而不仅仅是一次。

    StateName |   City     |    Name    
---------------------------------------
California    |   Oakland  |    Oprah  
Idaho         |   Boise    |    Mike  
Idaho         |   Boise    |    Joan 
New York      |   Gotham   |    Bruce
Washington    |   Seattle  |    Jake
Texas         |   Austin   |    John
Texas         |   Austin   |    Peter
Texas         |   Tyler    |    Jim

如果我想要实现的输出是可能的，有人会向我展示如何编写查询的示例，或者指出我正确的方向吗？

标签： mysql

解决方案

我同意 Tim，这应该在您的表示层中处理。

但是，如果您使用 MySQL 8，则可以利用窗口函数来做到这一点。我们可以使用row_number()来确定每个州和每个城市的第一行，然后使用case仅显示第一行的名称。

SELECT
  case row_number() over states_w
    when 1 then states.name
    else '' end as StateName,
  case row_number() over cities_w
    when 1 then cities.name
    else '' end as CityName,
  people.name as Name
FROM People
INNER JOIN Cities ON Cities.id = People.City_id
INNER JOIN States ON States.id = Cities.state_id
window states_w as (partition by states.id),
  cities_w as (partition by cities.id);

如果您在 8 之前使用 MySQL... 升级。如果不能升级，可以模拟。

推荐阅读