选择MYSQL行，但将行分为列，将列分为行 [英] Select MYSQL rows but rows into columns and column into rows

问题描述

我想选择数据库中的所有行，但是我希望它们以倒序排列.意思是，我想将第一列数据用作新实体，将当前实体用作第一列.我想你明白我的意思

I want to select all the rows in my database but I want them in inverted sequence. Meaning, I want to use the first column data as the new entities and present entities as the first column. I think you got what I mean

这是插图

id    |     name       | marks
-------------------------------
1     |    Ram         | 45
--------------------------------
2     |    Shyam       |  87

到

id    |   1     |    2     |
----------------------------
Name  |  Ram    |   Shyam  |
----------------------------
Marks |  45     |    87    | 

推荐答案

在已知的固定列数下，这是操作方法(我将表命名为"grades"是自由的):

With a fixed and known columns, here's how to do it (I took the liberty of naming the table "grades"):

要创建一个由不同查询组成的联合并执行它.

To create a union of different queries and execute it.

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

Since you need actual data as column headers, the first part of the union will look like:

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

仅该查询将复制结果，因此我们需要通过添加LIMIT 0, 0来告诉MySQL我们需要有0行.

That query alone will duplicate the result, therefore we need to tell MySQL we need to have 0 rows by adding LIMIT 0, 0.

我们联合的第一行将包含'Name'以及表中名称"列中的所有数据.要获得该行，我们需要一个查询，例如:

Our first row of the union will contain 'Name', as well as all the data from "Name" column of the table. To get that line we need a query like:

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

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

Using the same logic, our second row will look like:

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

获取标题:

我们需要从MySQL中产生一行，例如:

Getting the header:

We need to produce a row from MySQL like:

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

要获得这一行，我们将使用 CONCAT() 和 GROUP_CONCAT()函数:

To get that line we will use CONCAT() and GROUP_CONCAT() functions:

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

，我们将把该行存储到一个新变量中:

and we're going to store that line into a new variable:

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

创建线条:

我们需要创建两个如下查询:

Creating the lines:

We need to create two queries like the following:

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语句.可以使用以下方法生产它们:

Since we do not know in advance how many rows there are in our original table, we will be using variables to generate the different LIMIT x, 1 statements. They can be produced using the following:

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

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

Using this snippet, we can create our subqueries:

SELECT GROUP_CONCAT(
    CONCAT(' (SELECT name FROM grades LIMIT ',
        @a:=@a+1,
        ', 1)')
    )
FROM grades

我们将与第一列数据(这是第二列的名称)一起放入变量名称@ line1:

Which we will put into a variable names @line1, along with the first column data (which is the second column's name):

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

按照相同的逻辑，第二行将是:

By following the same logic, the second line will be:

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

将它们全部结合在一起:

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

Combining them all:

Our three variables now contain:

@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()创建一个最终变量，将其准备为新查询并执行:

We just need to create a final variable using CONCAT(), prepare it as a new query and execute it:

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

PREPARE my_query FROM @query;
EXECUTE my_query;

整个解决方案:

(供测试和参考):

Entire solution:

(for testing and reference):

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 rows in set (0.00 sec)

闭念:

• 我仍然不确定为什么需要将行转换为列，并且我确定我提出的解决方案不是最佳的解决方案(就性能而言).

  Closing thoughts:

  • I'm still not sure why you need to transform rows into columns, and I'm sure the solution I presented is not the best one (in terms of performance).

    您甚至可以使用我的解决方案作为起点，并使用information_schema.COLUMNS作为源将其适应于通用解决方案，在该解决方案中，表列名(和行数)未知. ，但我想这太过分了.

    You can even use my solution as a start and adapt it to a general purpose solution where the table column names (and the number of lines) are not known, using information_schema.COLUMNS as a source, but I guess that's just going too far.

    我坚信最好将原始表放入数组中，然后旋转该数组，从而以所需的格式获取数据.

    I strongly believe it is much better to put the original table into an array and then rotate that array, thus getting the data in the desired format.

    这篇关于选择MYSQL行，但将行分为列，将列分为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！