mysql - 使用 SQL 连接表作为额外列

Question

我正在尝试编写一个查询，从其他几个表中提取数据作为查询中的列。

这是数据:

用户表

ID    ACCOUNT_ID    FIRST_NAME        LAST_NAME
1         1             Joe            SMITH
2         1             Bill           WALTERS
3         1             Bill           JOHNSON

用户字段表

ID      ACCOUNT_ID     NAME
 1         1            CITY
 2         1            STATE
 3         2            EMPLOYEE_NUMBER
 4         3            MIDDLE_NAME

用户字段数据表

ID    USER_FIELD_ID      USER_ID     DATA
 1         1               1         LINCOLN
 2         2               1         NEBRASKA

我想要一个类似的查询:

SELECT FIRST_NAME, LAST_NAME FROM users WHERE ACCOUNT_ID=1

但我希望它包含 CITY 和 STATE 作为列，但这些列会根据 ACCOUNT_ID 和用户字段表中的数据而有所不同。

我认为这可以通过 PIVOT 来完成，但我很难让它发挥作用。谢谢!

Answer 1

使用此代码的行为类似于 PIVOT。您需要为所需的每个用户字段类型添加一个MAX(CASE)。由于它使用聚合，因此您只能从每个用户字段的用户字段数据中获取一个值

SELECT  u.FIRST_NAME, 
        u.LAST_NAME, 
        uf.CITY, 
        uf.STATE
FROM    users u
        LEFT JOIN (SELECT   uf.ACCOUNT_ID,
                            ufd.USER_ID,
                            MAX(CASE WHEN uf.NAME = 'CITY' THEN [DATA] END) AS CITY,
                            MAX(CASE WHEN uf.NAME = 'STATE' THEN [DATA] END) AS STATE
                   FROM     UserFields uf
                            JOIN UserFieldData ufd ON uf.ID = ufd.USER_FIELD_ID
                   GROUP BY uf.ACCOUNT_ID,
                            ufd.USER_ID
                  ) uf ON uf.USER_ID = u.ID
                          AND uf.ACCOUNT_ID = u.ACCOUNT_ID
WHERE   u.ACCOUNT_ID = 1