mysql - 仅用一条SELECT语句替换多个CREATE VIEW-MySQL

我有以下sql脚本：

CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `day0` AS
SELECT `c`.`customerId` AS `CustomerID`,SUM(`t`.`orderTotal`) AS `Day0`,
       `t`.`dateOfSale` AS `DateOfSale`,`c`.`initialDateOfSale` AS `InitialDateOfSale`
FROM `customer` `c` LEFT JOIN
     `transaction` `t`
      on `t`.`customerId` = `c`.`customerId`
where `t`.`status` = 2 and `t`.`dateOfSale` <= DATE_ADD(`c`.`initialDateOfSale`, interval 1 day)
GROUP BY `c`.`customerId';

CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `day30` AS
SELECT `c`.`customerId` AS `CustomerID`,SUM(`t`.`orderTotal`) AS `Day30`,
       `t`.`dateOfSale` AS `DateOfSale`,`c`.`initialDateOfSale` AS `InitialDateOfSale`
FROM `customer` `c` LEFT JOIN
     `transaction` `t`
     on `t`.`customerId` = `c`.`customerId`
where `t`.`status` = 2 and `t`.`dateOfSale` <= DATE_ADD(`c`.`initialDateOfSale`, interval 30 day)
GROUP BY `c`.`customerId`;

CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `day60` AS
SELECT `c`.`customerId` AS `CustomerID`,SUM(`t`.`orderTotal`) AS `Day60`,
       `t`.`dateOfSale` AS `DateOfSale`,`c`.`initialDateOfSale` AS `InitialDateOfSale`
FROM `customer` `c` LEFT JOIN
     `transaction` `t`
     on `t`.`customerId` = `c`.`customerId`
where `t`.`status` = 2 and `t`.`dateOfSale` <= DATE_ADD(`c`.`initialDateOfSale`, interval 60 day)
GROUP BY `c`.`customerId`;

SELECT day0.customerid,day0.day0, day30.day30, day60.day60,
FROM day0, day30, day60,
WHERE day0.customerid=day0.customerid and day0.customerid=day30.customerid and
      day0.customerid=day60.customerid

是否仍然可以用一个SELECT替换多个CREATE VIEW语句，而不在数据库中创建新的视图/表？除此之外，最终用户将没有创建视图权限。
谢谢您，
奥基夫

最佳答案

您可以通过一个简单的查询得到想要的结果：

SELECT c.`customerId` AS `CustomerID` ,
       SUM(case when `t`.`dateOfSale` <= DATE_ADD(`c`.`initialDateOfSale`, interval 1 day)
                then `t`.`orderTotal`
                else 0
           end) AS `Day0`,
       SUM(case when `t`.`dateOfSale` <= DATE_ADD(`c`.`initialDateOfSale`, interval 60 day)
                then `t`.`orderTotal`
                else 0
           end) AS `Day60`,
       SUM(case when `t`.`dateOfSale` <= DATE_ADD(`c`.`initialDateOfSale`, interval 90 day)
                then `t`.`orderTotal`
                else 0
           end) AS `Day90`,
       `t`.`dateOfSale` AS `DateOfSale`, `c`.`initialDateOfSale` AS `InitialDateOfSale`
FROM `customer` `c` LEFT JOIN
     `transaction` `t`
     on `t`.`customerId` = `c`.`customerId`
where `t`.`status` = 2
GROUP BY `c`.`customerId`;

可以围绕此查询创建视图。

关于mysql - 仅用一条SELECT语句替换多个CREATE VIEW-MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21997354/