我想在MySQL中创建一个视图,该视图将由3个表生成。
请参见下表。
我要做的就是创建一个视图,使用table:ItemList的col:name作为新视图的列标签。
如何使用SQL实现此目的?
表格:ItemList,此更改如此频繁
+----+-------------+
| id | name |
+----+-------------+
| 1 | Apple |
| 2 | Orange |
| 3 | Banana |
| 4 | Kiwi |
| 5 | Mango |
+----+-------------+
表格:UserList
+----+-------------+
| id | name |
+----+-------------+
| 1 | John |
| 2 | Mary |
| 3 | James |
+----+-------------+
表格: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 |
+----+------+------+-----+
我要创建的视图
+-------+-------+--------+--------+------+-------+
| User | Apple | Orange | Banana | Kiwi | Mango |
+-------+-------+--------+--------+------+-------+
| John | | 2 | 3 | 1 | |
| Mary | 4 | 2 | | | |
| James | | | | | 1 |
+-------+-------+--------+--------+------+-------+
您必须为此使用条件求和和动态SQL
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;
输出:
| NAME | 苹果| 橙色| 香蕉| 猕猴桃 芒果 -------------------------------------------------- - | 詹姆斯| (空)| (空)| (空)| (空)| 1 | | 约翰| (空)| 2 | 3 | 1 | (空)| | 玛丽| 4 | 2 | (空)| (空)| (空)|
这是SQLFiddle演示
现在,您将无法将其包装到视图中,但是可以将其设置为存储过程。
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 ;
并像这样使用它:
CALL sp_order_report();
这是SQLFiddle演示
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句