mysql - 根据嵌套查询的结果替换 sql 查询中的值

Question

我有两个表:请求和位置

请求的示例数据

request id | requestor | locations
     1     |   ankur   |   2,5
     2     |   akshay  |   1  
     3     |   avneet  |   3,4
     4     |   priya   |   4     

位置示例数据

loc_id     |  loc_name |
     1     |  gondor   
     2     |  rohan
     3     |  mordor   
     4     |  bree   
     5     |  shire    

我想找到特定位置的 request_id。如果我使用 location_id 执行此操作，我会得到正确的结果。

select request_id from request where locations like "%,3%" or locations like "%3,%";

此查询为我提供了针对位置 id = 3 提出的请求

我怎样才能为 loc_name 实现这个呢？将查询中“like”部分的数字替换为

select loc_id from locations where loc_name = "mordor"

任何对此的帮助都会非常有帮助。谢谢。

Answer 1

您可以使用FIND_IN_SET()

SELECT *
  FROM request r JOIN locations l
    ON FIND_IN_SET(loc_id, locations) > 0
 WHERE loc_name = 'mordor'

这是 SQLFiddle 演示

但是您可以更好地规范化您的数据，方法是引入一个看起来像这样的多对多表

CREATE TABLE request_location
(
  request_id INT NOT NULL,
  loc_id INT NOT NULL,
  PRIMARY KEY (request_id, loc_id),
  FOREIGN KEY (request_id) REFERENCES request (request_id),
  FOREIGN KEY (loc_id) REFERENCES locations (loc_id)
);

从长远来看，这将带来巨大返回，使您能够正常维护和查询数据。

你的查询可能看起来像

SELECT *
  FROM request_location rl JOIN request r 
    ON rl.request_id = r.request_id JOIN locations l
    ON rl.loc_id = l.loc_id
 WHERE l.loc_name = 'mordor'

甚至

SELECT rl.request_id
  FROM request_location rl JOIN locations l
    ON rl.loc_id = l.loc_id
 WHERE l.loc_name = 'mordor';

如果只需要返回request_id

这是 SQLFiddle 演示