php - 连接mysql中的三个表有奇怪的要求

Question

我的数据库中有三个表。

表 A 具有字段

KEYID | KeyName
27    | Income
28    | Account Number

表 B 包含字段

UserID | Email          | Name | Phone
481    | test@gmail.com | test | 99999999

表 C 具有字段

ID | KEYID | UserID | Value
1  |   27  |   481  | 10,000

我需要显示的表字段标题是:

UserID | Email          | Name |   Phone  | Income

表值应如下所示:

 481   | test@gmail.com | test | 99999999 | 10,000

我可以获得应显示在表中的KeyID。在此示例中，KeyIDs 字符串为 '27' 。我尝试加入，我可以获取并显示表中的值。但我不知道如何将键名称显示为表头。

有什么想法吗？

Answer 1

您可以使用一对内连接

select b.UserID, b.Email , b.Name, c.value as income 
from   tableB as b inner join tableC as C on b.userID = c.userId
inner join tableA as a on a.keyID = c.keyID 
and a.keyname = 'Income';     

以及您在评论中提供的查询

select 
      b.UserID
    , b.Email 
    , b.Name
    , Group_Concat(Distinct Concat(c.keyID,’^:^’,c.value) 
                          Order By c.id Separator ‘;’) As Keyvalues 
    from tableB as b 
    inner join tableC as C on b.userID = c.userId 
    inner join tableA as a on a.keyID = c.keyID;  

并且使用 CASE 应该是

 select 
      b.UserID
    , b.Email 
    , b.Name
    , Group_Concat(Distinct CASE 
            WHEN c.keyID IN ('1,23,10') THEN Concat(c.keyID,’^:^’,c.value) END  
            Order By c.id Separator ‘;’) As Keyvalues 
    from tableB as b 
    inner join tableC as C on b.userID = c.userId 
    inner join tableA as a on a.keyID = c.keyID;