两个表输出中的 MySQL 查询未正确生成

Question

我的第一个表“table1”:

------|----------|----------|
| Id  |   Name   | Location |
------|----------|----------|
| 1   | Ashish   |   1,2    |
------|----------|----------|
| 2   |  Jay     |    3     |
------|----------|----------|
| 3   | Sabir    |   4,1    |
------|----------|----------|

我的第二个表'table2':

 |------|----------|----------|
 | Id   |   Location_name     | 
 |------|----------|----------|
 |  1   |     Ahmedabad       |             
 |------|----------|----------|
 |  2   |       Keshod        |
 |------|----------|----------|
 |  3   |       Junagadh      |
 |----- |----------|----------|
 |  4   |       Rajkot        |
 |----- |----------|----------|

我尝试了这个查询，但没有正确生成输出:

Select t1.Name,t2.Location_name from table1 as t1 Left Join table2 as t2 ON t1.Location = t2.Id WHERE t1.Location IN (t1.Location)

将生成的输出是:

 |-----------|---------------------------|
 | Name      |   Location_name           | 
 |-----------|---------------------------|
 |  Ashish   |    Ahmedabad              |             
 |-----------|---------------------------|
 |  Jay      |    Junagadh               |             
 |-----------|---------------------------|
 |  Sabir    |    Rajkot                 |             
 |-----------|---------------------------|

但是我想要这个输出:

 |-----------|---------------------------|
 | Name      |   Location_name           | 
 |-----------|---------------------------|
 |  Ashish   |    Ahmedabad,Keshod       |             
 |-----------|---------------------------|
 |  Jay      |    Junagadh               |             
 |-----------|---------------------------|
 |  Sabir    |    Rajkot,Ahmedabad       |             
 |-----------|---------------------------|

Answer 1

试试这个查询

SELECT t1.name, GROUP_CONCAT(t2.location_name) FROM table1 t1, table2 t2
WHERE FIND_IN_SET(t2.id, t1.location)
GROUP BY t1.id

或使用 LEFT JOIN

SELECT t1.name,GROUP_CONCAT(t2.location_name) FROM table1 t1
LEFT JOIN table2 t2 ON FIND_IN_SET(t2.id, t1.location)
GROUP BY t1.id