mysql - SQL 分组依据并显示不同的值

Question

我想创建组查询，其中表值如下所示:

EMP_ID  ProjectID
815     1
985     1
815     3
985     4
815     4

我想要这样的输出

EMP_ID ProjectID1 ProjectID2 ProjectID3
815    1          3          4
985    1          4          0

谁能知道我如何在 SQL 查询中实现这个目标。

提前致谢。

Answer 1

捷径:

使用http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT
  tbl.emp_id,
  GROUP_CONCAT( DISTINCT project_id ) project_id_list
FROM tbl
GROUP BY tbl.emp_id

在这种情况下，您必须在应用程序中拆分/处理串联的 project_id_list 字符串(或 NULL)

漫长的道路:

我们将使用一个小技巧:

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TEMPORARY TABLE temp (
  emp_id      INT NOT NULL,
  -- project_num will count from 1 to N PER emp_id!
  project_num INT NOT NULL AUTO_INCREMENT,
  project_id  INT NOT NULL,
  PRIMARY KEY ( emp_id, project_num )
) ENGINE=MyISAM; -- works only with myisam!

生成每组自动增量:

INSERT INTO temp ( emp_id, project_id )
SELECT emp_id, project_id FROM tbl

计算需要多少个project_id列:

$MAX_PROJECTS_PER_EMP =
  SELECT MAX( max_projects_per_emp ) FROM
    ( SELECT COUNT(*) AS max_projects_per_emp project_id FROM tbl GROUP BY emp_id )

以编程方式创建选择表达式:

SELECT
  temp.emp_id,
  t1.project_id  AS project_id_1,
  t2.project_id  AS project_id_2,
  t98.project_id AS project_id_98,
  t99.project_id AS project_id_99,
FROM      temp
LEFT JOIN temp AS t1 ON temp.emp_id = t1.id AND t1.project_num = 1
LEFT JOIN temp AS t2 ON temp.emp_id = t2.id AND t1.project_num = 2
// create $MAX_PROJECTS_PER_EMP lines of LEFT JOINs
LEFT JOIN temp AS t98 ON temp.emp_id = t98.id AND t98.project_num = 98
LEFT JOIN temp AS t99 ON temp.emp_id = t99.id AND t99.project_num = 99