logo标签列表

将MYSQL的行选择变成列，将列转换为行

phpmysql
3
我想选择数据库中的所有行，但我希望它们按相反顺序排列。也就是说，我希望把第一列数据用作新实体，并把现有实体作为第一列。我想你知道我的意思。
以下是示意图：
id    |     name       | marks
-------------------------------
1     |    Ram         | 45
--------------------------------
2     |    Shyam       |  87

为了

id    |   1     |    2     |
----------------------------
Name  |  Ram    |   Shyam  |
----------------------------
Marks |  45     |    87    |
你需要在PHP中反转序列吗？我猜在SQL中这没有意义... - Boris Delormas
请查看https://dev59.com/AHM_5IYBdhLWcg3ww2EQ——但您能解释为什么需要这样做吗？可能有更好的解决方案。 - Konerak
@kaavier，我知道如何在PHP中实现，但我需要一个SQL解决方案。 - Starx
这可能无法在MySQL中完成。这不是它被设计来做的事情。 - Pekka
3个回答
16

如果列数是固定的且已知，下面是如何实现的（我给表格取了一个名称“grades”）：

总体思路：

创建不同的查询并将其合并执行。

由于您需要使用实际数据作为列标题，因此联合查询的第一部分将如下所示：

SELECT 'id', '1', '2', ....

那个查询会重复结果，因此我们需要告诉MySQL我们需要通过添加LIMIT 0, 0来获取0行。

我们联接的第一行将包含'Name'以及表中“Name”列的所有数据。要获取该行，我们需要执行以下查询：

SELECT 'Name',
    (SELECT Name FROM grades LIMIT 0, 1),
    (SELECT Name FROM grades LIMIT 1, 1),
    (SELECT Name FROM grades LIMIT 2, 1),
    ...

使用相同的逻辑，我们的第二行将如下所示：

SELECT 'Marks',
    (SELECT Marks FROM grades LIMIT 0, 1),
    (SELECT Marks FROM grades LIMIT 1, 1),
    (SELECT Marks FROM grades LIMIT 2, 1),
    ...

获取表头：

我们需要从MySQL生成一行，例如：

SELECT 'id', '1', '2', ... LIMIT 0, 0;

要获取该行，我们将使用CONCAT()和GROUP_CONCAT()函数：

SELECT 'id', 
    (SELECT GROUP_CONCAT(CONCAT(' \'', id, '\'')) FROM grades)
LIMIT 0, 0;

我们将把这行代码存储到一个新变量中：

SET @header = CONCAT('SELECT \'id\', ',
    (SELECT GROUP_CONCAT(CONCAT(' \'', id, '\'')) FROM grades),
    ' LIMIT 0, 0');

创建线条:

我们需要创建两个类似以下的查询:

SELECT 'Name',
    (SELECT Name FROM grades LIMIT 0, 1),
    (SELECT Name FROM grades LIMIT 1, 1),
    (SELECT Name FROM grades LIMIT 2, 1),
    ...

由于我们事先不知道原始表中有多少行，因此我们将使用变量生成不同的LIMIT x, 1语句。可以使用以下方法生成它们：

SET @a = -1;
SELECT @a:=@a+1 FROM grades;

使用此代码片段，我们可以创建子查询：

SELECT GROUP_CONCAT(
    CONCAT(' (SELECT name FROM grades LIMIT ',
        @a:=@a+1,
        ', 1)')
    )
FROM grades
我们将把它与第一列数据（即第二列的名称）放入名为 @line1 的变量中。
SET @a = -1;
SET @line1 = CONCAT(
    'SELECT \'Name\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Name FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

按照同样的逻辑，第二行将是：

SET @a := -1;
SET @line2 = CONCAT(
    'SELECT \'Marks\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Marks FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

将它们合并：

我们的三个变量现在包含：

@header:
SELECT 'id',  '1', '2' LIMIT 0, 0

@line1:
SELECT 'Name', (SELECT Name FROM grades LIMIT 0, 1),
    (SELECT name FROM grades LIMIT 1, 1)

@line2:
SELECT 'Marks', (SELECT Marks FROM grades LIMIT 0, 1),
    (SELECT marks FROM grades LIMIT 1, 1)
我们只需要使用CONCAT()创建一个最终变量，将其准备为新查询并执行它：
SET @query = CONCAT('(',
    @header,
    ') UNION (',
    @line1,
    ') UNION (',
    @line2,
    ')'
);

PREPARE my_query FROM @query;
EXECUTE my_query;

整体解决方案:

(用于测试和参考):

SET @header = CONCAT('SELECT \'id\', ',
    (SELECT GROUP_CONCAT(CONCAT(' \'', id, '\'')) FROM grades),
    ' LIMIT 0, 0');

SET @a = -1;
SET @line1 = CONCAT(
    'SELECT \'Name\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Name FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

SET @a := -1;
SET @line2 = CONCAT(
    'SELECT \'Marks\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Marks FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

SET @query = CONCAT('(',
    @header,
    ') UNION (',
    @line1,
    ') UNION (',
    @line2,
    ')'
);

PREPARE my_query FROM @query;
EXECUTE my_query;

输出：

+-------+------+-------+
| id    | 1    | 2     |
+-------+------+-------+
| Name  | Ram  | Shyam |
| Marks | 45   | 87    |
+-------+------+-------+
共2行（0.00秒）

结束语:

  • 我仍然不确定为什么需要将行转换为列，并且我相信我提出的解决方案并不是最好的（从性能角度考虑）。

  • 您甚至可以使用我的解决方案作为起点，并使用information_schema.COLUMNS作为数据源，将表列名（和行数）未知的表转换为通用解决方案，但我认为这只是过于繁琐了。

  • 我强烈建议将原始表格放入数组中，然后旋转该数组，从而获得所需格式的数据。

0
听起来你需要的是对数据进行交叉表（或转置）操作。
这里有一篇优秀的文章描述了如何完成这个操作：http://onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html - 尽管它有点过时，但我不确定现在是否有更有效的方法来完成这个操作。对我来说，它已经足够好了。
-1
在命令行中，您可以以 \G 结束查询以实现此目的，例如：
select * from `students`\G;
有趣。但我不认为这是 OP 寻找的内容。它会将每一行显示为一个块，而不是每一行并排显示。 - knittl
同意。而且我不确定这是否适用于PHP的MySQL函数，但这是我所知道的最接近的东西 :) - Gunjan
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接