mysql - 多连接查询，透视？烦恼

Question

提前感谢任何对此提供帮助的人。我知道我以前做过这件事，没有太多痛苦，但似乎找不到解决方案

我的数据库看起来像这样:

`tbl_user:
----------
id         ( pkey )
email
fName
lName

tbl_userSparseType:
-------------------
id         ( pkey )
varName
displayName

tbl_userSparse:
---------------
id         ( pkey )
value      ( Value of Sparse Type )
user_id    ( => tbl_user.id )
userSparseType_id     ( => tbl_userSparseType.id )

带有示例数据:

tbl_user:
(id,   email,              fName,   lName)
 1     Bob@example.com     Billy    Bob
 2     Betty@example.com   Betty    Sue
 3     Beam@example.com    Jim      Beam

tbl_userSparseType:
(id,   varName,    displayName)
 1     fullName    Full Name
 2     dayPhone    Day Phone
 3     nightPhone  Night Phone
 4     cellPhone   Cell Phone
 5     homeAddr    Home Address

tbl_userSparse:
(id,    value,             user_id,    userSparseType_id)
 1      Billy John Bob     1           1
 2      James B. Beam      3           1
 3      123-234-3456       1           2
 4      234-345-4567       1           4
 5      24 Best st.        2           5
 6      456-567-6789       3           3

我尝试进行两次左连接，但这为每个稀疏条目提供了一个 tbl_user 行，例如:

(id, email,            fName,  lName, displayName, value)
 1,"Bob@example.com","Billy","Bob","Full Name","Billy John Bob"
 1,"Bob@example.com","Billy","Bob","Day Phone","123-234-3456"
 1,"Bob@example.com","Billy","Bob","Cell Phone","234-345-4567"

尽管进行了大约 45 分钟的查找，但我无法找到一种方法来获得更像以下内容而不明确命名列，我需要一种动态方法来仅提取适用于子集的所有显示名称正在查询的 tbl_user 行:

WHERE tbl_user.id IN (1,2)

 id | email             | fName | lName | Full Name,     | Day Phone    | Cell Phone   | 
Home Address
-------------------------------------------------------------------------------------------------------
 1  | Bob@example.com   | Billy | Bob   | Billy John Bob | 123-234-3456 | 234-345-4567 |
 2  | Betty@example.com | Betty | Sue   |                |              |              | 24 Best St.

再次提前致谢，我希望这可以顺利完成。 :\

Answer 1

不幸的是，MySQL 没有 PIVOT 函数，这基本上就是您要尝试做的。因此，您需要使用带有 CASE 语句的聚合函数。如果您知道列数，则可以对值进行硬编码:

select u.id, 
  u.email,
  u.fname,
  u.lname,
  max(case when t.displayname = 'Full Name' then us.value end) FullName,
  max(case when t.displayname = 'Day Phone' then us.value end) DayPhone,
  max(case when t.displayname = 'Cell Phone' then us.value end) CellPhone,
  max(case when t.displayname = 'Home Address' then us.value end) HOmeAddress
from tbl_user u
left join tbl_userSparse us
  on u.id = us.user_id
left join tbl_userSparseType t
  on us.userSparseType_id = t.id
where u.id in (1, 2) 
group by u.id, u.email, u.fname,u.lname;

参见 SQL Fiddle With Demo

现在，如果您想动态地执行此操作，这意味着您事先不知道要转置的列，那么您应该查看以下文章:

Dynamic pivot tables (transform rows to columns)

您的代码如下所示:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t.displayname = ''',
      t.displayname,
      ''' then us.value end) AS ',
      replace(t.displayname, ' ', '')
    )
  ) INTO @sql
FROM tbl_userSparse us
left join tbl_userSparseType t
  on us.userSparseType_id = t.id;

SET @sql = CONCAT('SELECT u.id, u.email, u.fname, u.lname, ', @sql, ' 
                  from tbl_user u
                  left join tbl_userSparse us
                    on u.id = us.user_id
                  left join tbl_userSparseType t
                    on us.userSparseType_id = t.id
                  where u.id in (1, 2) 
                  group by u.id, u.email, u.fname, u.lname');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见 SQL Fiddle with Demo