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

Question

我有以下 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 语句而不在数据库中创建新的 View /表？除此之外，最终用户将没有 CREATE VIEW 权限。

谢谢，

奥基夫

Answer 1

您可以通过一个更简单的查询获得您想要的内容:

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`;

您可以围绕此查询创建 View 。