mysql把一个表的内容替换,如何将单元格中的文本替换为mysql中另一个表中的数据...

I need you help.

I have a table in MYSQL like this

*TABLE1*

id | permission

-- | ----------

a1 | 1,2,3,4,5

v2 | 2,3,4

r1 | 1,3,4,5

b4 |

h7 | 4,5

and i have another table

*TABLE2*

id | permission

-- | ----------

1 | Allow

2 | Not Allow

3 | Disabled

4 | WOW

5 | Grant

I want to select TABLE1 JOIN TABLE2 BUT: The new table should be like this

*NEW_TABLE*

id | permission

-- | ----------

a1 | Allow,Not Allow,Disabled,WOW,Grant

v2 | Not Allow,Disabled,WOW

r1 | Allow,Disabled,WOW,Grant

b4 |

h7 | WOW,Grant

Is there a way to make it in MYSQL?

解决方案

well you could try something along these lines:

this is test setup:

mysql> select * from table1;

+------+------------+

| id | permission |

+------+------------+

| a1 | 1, 2, 3, 4 |

| v2 | 2, 3, 4 |

+------+------------+

2 rows in set (0.01 sec)

mysql> select * from table2;

+------+------------+

| id | permission |

+------+------------+

| 1 | Allow |

| 2 | Not Allow |

| 3 | Disabled |

+------+------------+

3 rows in set (0.01 sec)

mysql> select id, GROUP_CONCAT(t2perm) from (SELECT t1.*, t2.id as t2id, t2.permission as t2perm from table2 t2 cross join table1 t1) crs where INSTR(permission, t2id) > 0 group by id;

+------+--------------------------+

| id | GROUP_CONCAT(t2perm) |

+------+--------------------------+

| a1 | Allow,Not Allow,Disabled |

| v2 | Not Allow,Disabled |

+------+--------------------------+

2 rows in set (0.00 sec)

To explain a bit; first you cross join both tables and that should result in cartesian product, like so:

mysql> SELECT * from table2 cross join table1;

+------+------------+------+------------+

| id | permission | id | permission |

+------+------------+------+------------+

| 1 | Allow | a1 | 1, 2, 3, 4 |

| 1 | Allow | v2 | 2, 3, 4 |

| 2 | Not Allow | a1 | 1, 2, 3, 4 |

| 2 | Not Allow | v2 | 2, 3, 4 |

| 3 | Disabled | a1 | 1, 2, 3, 4 |

| 3 | Disabled | v2 | 2, 3, 4 |

+------+------------+------+------------+

6 rows in set (0.00 sec)

from that point, just select rows that have one string contained in another (INSTR(permission, t2id) => mapping permissions onto ids), you'll end up with this:

mysql> select * from (SELECT t1.*, t2.id as t2id, t2.permission as t2perm from table2 t2 cross join table1 t1) crs where INSTR(permission, t2id) > 0;

+------+------------+------+-----------+

| id | permission | t2id | t2perm |

+------+------------+------+-----------+

| a1 | 1, 2, 3, 4 | 1 | Allow |

| a1 | 1, 2, 3, 4 | 2 | Not Allow |

| v2 | 2, 3, 4 | 2 | Not Allow |

| a1 | 1, 2, 3, 4 | 3 | Disabled |

| v2 | 2, 3, 4 | 3 | Disabled |

+------+------------+------+-----------+

5 rows in set (0.00 sec)

now just aggregate results with GROUP_CONCAT...

select id, GROUP_CONCAT(t2perm) from (SELECT t1.*, t2.id as t2id, t2.permission as t2perm from table2 t2 cross join table1 t1) crs where INSTR(permission, t2id) > 0 group by id;