带组联系人的 MySQL 查询

Question

假设我有一个名为“test”的表，其设计如下:

SELECT type, name, `key` FROM test

type | name    | key------------------------  0  | maria   | 123  1  | gabriel | 455  0  | rihanna | 69  1  | chris   | 7  1  | martin  | 112

 

 

The next query allows me to get all data in one line:

SELECT GROUP_CONCAT(type ORDER BY type) types, GROUP_CONCAT(name ORDER BY type) names, GROUP_CONCAT(`key` ORDER BY type) `keys` FROM test

  types   |               names                |      keys------------------------------------------------------------------0,0,1,1,1 | rihanna,maria,martin,chris,gabriel | 69,123,112,7,455

但这并不是我所需要的。如果我能够创建返回以下结果的查询，那就太完美了:

types_0 |     names_0    |  keys_0  | types_1 |         names_1         |    keys_1------------------------------------------------------------------------------------  0, 0  | maria, rihanna |  123, 69 |   1, 1  | gabriel, chris, martin  | 455, 7, 112

有什么方法可以创建这样的查询吗？还是根本没有意义？

提前致谢。

Answer 1

这是可能的，但我不会这样做。它看起来像这样:

SELECT * FROM 
(
  SELECT 
    GROUP_CONCAT(type ORDER BY type) types, 
    GROUP_CONCAT(name ORDER BY type) names, 
    GROUP_CONCAT(`key` ORDER BY type) `keys` 
  FROM test
  WHERE type = 0
) AS _type0,
(
  SELECT 
    GROUP_CONCAT(type ORDER BY type) types, 
    GROUP_CONCAT(name ORDER BY type) names, 
    GROUP_CONCAT(`key` ORDER BY type) `keys` 
  FROM test
  WHERE type = 1
) AS _type1;

如果找到更多类型，则无法动态生成更多列。这是典型的数据透视表查询——您必须在编写查询之前知道不同的值。

我会这样做:

SELECT 
  type,
  GROUP_CONCAT(name ORDER BY name) names, 
  GROUP_CONCAT(`key` ORDER BY name) `keys` 
FROM test
GROUP BY type;

输出应该是这样的:

 type |         names        |  keys
------------------------------------------------------------------
 0    | maria,rihanna        | 123,69
 1    | chris,gabriel,martin | 7,455,112

编辑:根据@GarethD 的回答的建议，我在每个组中按name 进行了此查询排序。