将交叉表查询转换为视图

Question

我有以下查询，我想转换为视图：

SELECT
  PartNum,
  SUM(IF(DAYOFWEEK(DeliveryDate) = '2', value, NULL)) AS 'Mon',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '3', value, NULL)) AS 'Tue',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '4', value, NULL)) AS 'Wed',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '5', value, NULL)) AS 'Thu',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '6', value, NULL)) AS 'Fri',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '7', value, NULL)) AS 'Sat',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '1', value, NULL)) AS 'Sun',
  SUM(IF(DeliveryDate > DATE_ADD(CURDATE(),INTERVAL 7 DAY), value, NULL)) AS 'Future'
FROM (
  SELECT PartNum, DeliveryDate , SUM(Ordered) value FROM v_archived_items_due
    GROUP BY PartNum, DeliveryDate
    ) t
  GROUP BY PartNum;

当我试图将其保存为视图时，会得到以下错误：

1349 -视图选择在FROM子句中包含一个子查询。

查询本身运行良好。我怎样才能把它变成风景？

Answer 1

MySQL不允许查看子查询，因此，如果不查看任何示例数据，则无法查看如何在没有子查询的情况下重新处理该子查询。由于此操作如出一辙，我将创建您的子查询的视图：

create view view1 as
    SELECT PartNum, DeliveryDate , SUM(Ordered) value 
    FROM v_archived_items_due
    GROUP BY PartNum, DeliveryDate;

然后，只需在查询中调用此视图：

SELECT
  PartNum,
  SUM(IF(DAYOFWEEK(DeliveryDate) = '2', value, NULL)) AS 'Mon',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '3', value, NULL)) AS 'Tue',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '4', value, NULL)) AS 'Wed',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '5', value, NULL)) AS 'Thu',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '6', value, NULL)) AS 'Fri',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '7', value, NULL)) AS 'Sat',
  SUM(IF(DAYOFWEEK(DeliveryDate) = '1', value, NULL)) AS 'Sun',
  SUM(IF(DeliveryDate > DATE_ADD(CURDATE(),INTERVAL 7 DAY), value, NULL)) AS 'Future'
FROM view1
GROUP BY PartNum;

Answer 2

按照设计的行为

http://dev.mysql.com/doc/refman/5.1/en/create-view.html

您必须从您的子查询构建一个视图，并使用此视图。