mysql - 减少mysql中同一个父表的连接数

Question

我有一个包含用户信息的用户表和一个映射到具有用户 ID 的用户表的签名表，如下所示

    users      userid | firstname | lastname        1   |   John       | P        2   |   Pete       | C        3   |   Tim        | D

  signs     doneBy | CheckedBy | VerifiedBy       1    |   2       | 3

有没有更好的方法来代替下面的多个连接，考虑到我会有 6 个这样的连接

    select       concat(usr1.firstname, ' ', usr1.lastname) as doneby,      concat(usr2.firstname, ' ', usr2.lastname) as checkedby,      concat(usr3.firstname, ' ', usr3.lastname) as verifiedby    from signs sgn      join users usr1 on ( usr1.userid = sgn.doneBy)        join users usr2 on ( usr2.userid = sgn.checkedBy)        join users usr3 on ( usr3.userid = sgn.verifiedBy)  

Answer 1

您可以使用 CASE EXPRESSION 进行条件聚合，如下所示:

SELECT MAX(CASE WHEN t.doneBy = t.userId THEN t.full_name END) AS doneBy,
       MAX(CASE WHEN t.CheckedBy = t.userId THEN t.full_name END) AS doneBy,
       MAX(CASE WHEN t.VerifiedBy = t.userId THEN t.full_name END) AS doneBy
FROM (
     SELECT u.userid,concat(u.firstname, ' ', u.lastname) as full_name,u.*
     FROM signs s
     INNER JOIN users u
      ON(s.userid IN(s.doneBy,s.CheckedBy,s.VerifiedBy))) t
GROUP BY t.doneBy,t.checkBy,t.VerifiedBy