mysql - 将多个查询的结果插入到新表中

Question

我有这些查询，我想将这些结果插入到单个临时表中。我怎样才能做到这一点？

select date(max(created_date)) AS 'Last Shipped Date',
sum(item_count) AS 'Last Shipped Units'
from order 
where is_shipped = 1
AND date(shipped_date) = (select date(max(shipped_date)) from order); 

select 
count(distinct o.key) AS 'ACTIVE ',
count(od.ean) AS 'Active_ Units'
from order o 
left join order_details od on o.id = od. order_details
Where o.is_active = 1;

select count(distinct order_key) AS 'Total_Orders_Shipped_Yesterday',
sum(item_count) AS 'Total_units_Shipped_yesterday' 
from order
where datediff(curdate(), modified_date)=1
AND is_shipped =1;


select count(distinct liquidation_order_id) AS 'orders cancelled',
count(ean) AS 'Units cancelled'
from order_details
where datediff(curdate(), modified_date)=1
AND order_details_status_ =4;

Answer 1

可能有一种方法可以在一个查询中完成此操作，但会很复杂。只需执行一系列 UPDATE 查询，通过与计算值的其他查询连接来填充表中相应的列，会更容易。

CREATE TEMPORARY TABLE tempTable (
    `Last Shipped Date` DATE,
    `Last Shipped Units` INT,
    Active INT,
    Active_Units INT,
    Total_Orders_Shipped_Yesterday INT,
    Total_units_Shipped_yesterday INT,
    `orders cancelled` INT,
    `Units cancelled` INT);

INSERT INTO tempTable (`Last Shipped Date`, `Last Shipped Units`)
select date(max(created_date)) AS 'Last Shipped Date',
sum(item_count) AS 'Last Shipped Units'
from order 
where is_shipped = 1
AND date(shipped_date) = (select date(max(shipped_date)) from order); 

UPDATE tempTable AS t
JOIN order o 
left join order_details od on o.id = od. order_details
SET t.Active = count(distinct o.key), t.Active_Units = count(od.ean)
Where o.is_active = 1;

UPDATE tempTable AS t
JOIN order
SET t.Total_Orders_Shipped_Yesterday = count(distinct order_key),
    t.Total_units_Shipped_yesterday = SUM(item_count)
where datediff(curdate(), modified_date)=1
    AND is_shipped =1;

UPDATE tempTable AS t
JOIN order_details
SET t.`orders cancelled` = count(distinct liquidation_order_id),
    t.`Units cancelled` = COUNT(ean)
where datediff(curdate(), modified_date)=1
    AND order_details_status_ =4;