SQL创建矩阵表 [英] SQL to create matrix table

问题描述

我想在 MySQL 中创建一个视图，该视图将从 3 个表中生成.

请参阅下表.

我想要做的就是创建一个视图，使用 col:name of table:ItemList 作为新视图的列的标签.

如何使用 SQL 实现这一点?

table: ItemList ，这个变化太频繁了

+----+-------------+|身份证 |姓名 |+----+------------+|1 |苹果 ||2 |橙色 ||3 |香蕉 ||4 |猕猴桃 ||5 |芒果 |+----+------------+

表:用户列表

+----+-------------+|身份证 |姓名 |+----+------------+|1 |约翰 ||2 |玛丽 ||3 |詹姆斯 |+----+------------+

表格:订单列表

+----+------+------+------+-----+|身份证 |用户 |项目 |数量 |+----+------+------+------+|1 |1 |4 |1 ||2 |1 |2 |2 ||3 |2 |1 |4 ||4 |1 |3 |3 ||5 |3 |5 |1 ||6 |2 |2 |2 |+----+------+------+------+

查看我想创建的

+-------+-------+--------+--------+------+-------+|用户 |苹果 |橙色 |香蕉 |猕猴桃 |芒果 |+-------+-------+--------+--------+------+-------+|约翰 ||2 |3 |1 |||玛丽 |4 |2 |||||詹姆斯 |||||1 |+-------+-------+--------+--------+------+-------+

解决方案

你必须为此使用条件求和和动态 SQL

SET @sql = NULL;选择GROUP_CONCAT(DISTINCT)康卡特('SUM(CASE WHEN l.Item = ',ID，' THEN l.qty END) `',名称，'`')) 进入@sql从项目列表；SET @sql = CONCAT('SELECT u.name, ', @sql, 'FROM OrderList l JOIN UserList uON l.User = u.idGROUP BY u.name');从@sql 准备 stmt;执行 stmt;解除分配准备 stmt;

输出:

<前>|姓名 |苹果 |橙色 |香蕉 |猕猴桃 |芒果 |------------------------------------------------------|詹姆斯 |(空) |(空) |(空) |(空) |1 ||约翰 |(空) |2 |3 |1 |(空) ||玛丽 |4 |2 |(空) |(空) |(空) |

这是SQLFiddle演示

现在你不能把它包装成一个视图，但你可以把它变成一个存储过程.

DELIMITER $$创建程序 sp_order_report()开始SET @sql = NULL;选择GROUP_CONCAT(DISTINCT)康卡特('SUM(CASE WHEN l.Item = ',ID，' THEN l.qty END) `',名称，'`')) 进入@sql从项目列表；SET @sql = CONCAT('SELECT u.name, ', @sql, 'FROM OrderList l JOIN UserList uON l.User = u.idGROUP BY u.name');从@sql 准备 stmt;执行 stmt;解除分配准备 stmt;完$$分隔符；

像这样使用它:

CALL sp_order_report();

这是SQLFiddle演示

I want to make a view in MySQL that will be generated from 3 tables.

Please see the following tables.

All I want to do is to create a view, using col:name of table:ItemList as the labels of the columns of the new view.

How can I achieve this using SQL?

table: ItemList ,this changes so frequently

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Apple       |
|  2 | Orange      |
|  3 | Banana      |
|  4 | Kiwi        |
|  5 | Mango       |
+----+-------------+

table: UserList

+----+-------------+
| id | name        |
+----+-------------+
|  1 | John        |
|  2 | Mary        |
|  3 | James       |
+----+-------------+

table: OrderList

+----+------+------+-----+
| id | User | Item | qty |
+----+------+------+-----+
|  1 |    1 |    4 |   1 |
|  2 |    1 |    2 |   2 |
|  3 |    2 |    1 |   4 |
|  4 |    1 |    3 |   3 |
|  5 |    3 |    5 |   1 |
|  6 |    2 |    2 |   2 |
+----+------+------+-----+

view that I want to create

+-------+-------+--------+--------+------+-------+
| User  | Apple | Orange | Banana | Kiwi | Mango |
+-------+-------+--------+--------+------+-------+
| John  |       |      2 |      3 |    1 |       |
| Mary  |     4 |      2 |        |      |       |
| James |       |        |        |      |     1 |
+-------+-------+--------+--------+------+-------+

解决方案

You have to use conditional summation and dynamic SQL for this

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN l.Item = ',
      id,
      ' THEN l.qty END) `',
      name, '`'
    )
  ) INTO @sql
FROM ItemLIst;

SET @sql = CONCAT('SELECT u.name, ', @sql, ' 
                     FROM OrderList l JOIN UserList u
                       ON l.User = u.id
                    GROUP BY u.name');

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

Output:

|  NAME |  APPLE | ORANGE | BANANA |   KIWI |  MANGO |
------------------------------------------------------
| James | (null) | (null) | (null) | (null) |      1 |
|  John | (null) |      2 |      3 |      1 | (null) |
|  Mary |      4 |      2 | (null) | (null) | (null) |

Here is SQLFiddle demo

Now you won't be able to wrap it into a view, but you can make it a stored procedure.

DELIMITER $$
CREATE PROCEDURE sp_order_report()
BEGIN
  SET @sql = NULL;
  SELECT
    GROUP_CONCAT(DISTINCT
      CONCAT(
        'SUM(CASE WHEN l.Item = ',
        id,
        ' THEN l.qty END) `',
        name, '`'
      )
    ) INTO @sql
  FROM
    ItemLIst;
  SET @sql = CONCAT('SELECT u.name, ', @sql, ' 
                       FROM OrderList l JOIN UserList u
                         ON l.User = u.id
                      GROUP BY u.name');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

And use it like this:

CALL sp_order_report();

Here is SQLFiddle demo

这篇关于SQL创建矩阵表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！