php - Mysql 查询针对类别显示同一列两次

Question

我有一个表名 user，在此表中用户有他们的姓名和类别(例如:学生、员工)。

现在我想将其他表中的用户显示为按类型分隔的用户，例如所有学生用户将显示在学生列中，所有员工用户将显示在员工列中。

示例:

用户表:

Name       Category
John       Student
Jack       Student
Fred       Employee

输出表:

ID    Student    Employee
1     John     
2     Jack
3                 Fred

显示该表的查询是什么。

Answer 1

SELECT  ID,
        MAX(CASE WHEN Category = 'Student' THEN Name ELSE NULL END) Student,
        MAX(CASE WHEN Category = 'Employee' THEN Name ELSE NULL END) Employee
FROM    User
GROUP   BY ID

• SQLFiddle Demo

结果

╔════╦═════════╦══════════╗
║ ID ║ STUDENT ║ EMPLOYEE ║
╠════╬═════════╬══════════╣
║  1 ║ John    ║ (null)   ║
║  2 ║ Jack    ║ (null)   ║
║  3 ║ (null)  ║ Fred     ║
╚════╩═════════╩══════════╝

更新1

如果您有其他类别，则首选动态sql，

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when Category = ''',
      Category,
      ''' then Name end) AS ',
      Category
    )
  ) INTO @sql
FROM User;

SET @sql = CONCAT('SELECT  ID, ', @sql, ' 
                    FROM    User
                    GROUP   BY ID');

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

• SQLFiddle Demo
• SQLFiddle Demo (with other category)