mysql - 查询规范化数据库，3张表

Question

我在 MySQL 数据库中有三个表:

• 商店(PK stores_id)
• 州(PK states_id)
• join_stores_states(PK join_id，FK stores_id，FK states_id)

“stores”表中的每一项业务都有一行。 join_stores_states 表将单个企业链接到它所在的每个州。因此，一些企业在 3 个州有商店，因此它们在 join_stores_states 中有 3 行，而其他企业在 1 个州有商店，因此它们在 join_stores_states 中只有 1 行。

我正在尝试弄清楚如何编写一个查询，该查询将在一行中列出每个企业，但仍显示其所处的所有状态。

这是我目前所拥有的，这显然给了我 join_stores_states 的每一行:

SELECT states.*, stores.*, join_stores_states.* 
FROM join_stores_states 
JOIN stores 
ON join_stores_states.stores_id=stores.stores_id 
JOIN states 
ON join_stores_states.states_id=states.states_id

松散地，这就是它给我的:

• 商店 1 |阿拉巴马州
• 商店 1 |佛罗里达州
• 商店 1 |堪萨斯
• 商店 2 |蒙大拿
• 商店 3 |格鲁吉亚
• 商店 3 |佛蒙特州

这是我更想看到的:

• 商店 1 |阿拉巴马州、佛罗里达州、堪萨斯州
• 商店 2 |蒙大拿
• 商店 3 |佐治亚州、佛蒙特州

关于尝试使用哪种查询方法的建议将与有效查询一样受欢迎。

Answer 1

如果您需要状态列表作为字符串，您可以使用 MySQL 的 GROUP_CONCAT 函数(或等效函数，如果您使用的是另一种 SQL 方言)，如下例所示。如果您想分别对状态进行任何类型的进一步处理，我希望您像以前那样运行查询，然后将结果集收集到更复杂的结构中(数组哈希表，作为最简单的度量，但更复杂的 OO设计当然是可能的)在客户端通过迭代结果行。

SELECT stores.name,
  GROUP_CONCAT(states.name ORDER BY states.name ASC SEPARATOR ', ') AS state_names
FROM join_stores_states 
JOIN stores 
  ON join_stores_states.stores_id=stores.stores_id 
JOIN states 
  ON join_stores_states.states_id=states.states_id
GROUP BY stores.name

此外，即使您只需要拼接的字符串而不是数据结构，某些数据库可能没有聚合拼接功能，在这种情况下您仍然必须进行客户端处理。在伪代码中，因为您也没有指定语言:

perform query
stores = empty hash
for each row from query results:
  get the store object from the hash by name
  if the name isn't in the hash:
    put an empty store object into the hash under the name
  add the state name to the store object's stores array