字段中的mysql数组与其他表连接

Question

我有 2 个表:reason，user

reason (id, reason)
user (id, name, reason_id)

在数据中:

reason (1, 'ok'), (2, 'no problem')
user (1, 'eko', '1,2')

我需要这样的 View :

    id | name | reason
    1  | eko  | ok, no problem

这可能是为了这样做吗？

Answer 1

试试这个解决方案:

SELECT
    a.id, 
    a.name,
    GROUP_CONCAT(b.reason ORDER BY b.id SEPARATOR ', ') AS reason
FROM
    user a
INNER JOIN
    reason b ON SUBSTRING(a.reason, FIND_IN_SET(b.id, a.reason) + (FIND_IN_SET(b.id, a.reason) - 1), 1) = b.id
GROUP BY
    a.id

作为旁注，您拥有的是糟糕的数据库设计。 users 和 reasons 之间的关系是 N:M(多对多)关系。 一个用户可能有很多原因；许多用户可能都有一个原因 ...

对这种关系建模的最好方法是使用一个交叉引用表来存储 userid 和 reasonid 的唯一组合:

+-------------+   +-------------------+   +---------------+
| users       |   | users_has_reasons |   | reasons       |
+-------------+   +-------------------+   +---------------+
| userid [PK] |   | userid [PK]       |   | reasonid [PK] |
| name        |   | reasonid [PK]     |   | reason        |
| etc...      |   +-------------------+   | etc...        |
+-------------+                           +---------------+

然后为了获得相同的结果，您可以这样做:

SELECT
    a.userid, 
    a.name,
    GROUP_CONCAT(c.reason ORDER BY c.reasonid SEPARATOR ', ') AS reason
FROM
    users a
INNER JOIN
    users_has_reasons b ON a.userid = b.userid
INNER JOIN 
    reasons c ON b.reasonid = c.reasonid
GROUP BY
    a.userid

这样MUCH效率更高，因为您的联接将发生在索引上，而且MUCH更易于管理(即插入、更新、删除等)。