使用外部连接连接2个或更多sql查询

cngwdvgl 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(370)

我试图返回使用'outer join'的5个sql语句的结果，我想把过去5天的stock\u id、stock\u name和每天的销售额总和作为单独的列返回。我如何构建它？

select s1.sid as sid,s1.sname as sname, sum1,sum2,sum3,sum4,sum5
from
(
 select stock.id as sid, stock.Name as sname, sum(orders.Quantity) as 'sum1' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-16%' group by Stock_id asc
) 
as s1
full outer join
(
select stock.id as sid,stock.Name as sname, sum(orders.Quantity) as 'sum2' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-17%' group by Stock_id asc
)
as s2 on s1.sid=s2.sid 
outer join
(
select stock.id as sid,stock.Name as sname, sum(orders.Quantity) as 'sum3' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-18%' group by Stock_id asc
) as s3 on s1.sid=s3.sid 
outer join
( 
select stock.id as sid,stock.Name as sname,sum(orders.Quantity) as 'sum4' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-19%' group by Stock_id asc
)as s4 on s1.sid=s4.sid 
outer join
(
select stock.id as sid,stock.Name as sname, sum(orders.Quantity) as 'sum5' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-20%' group by Stock_id asc
)as s5 on s1.sid=s5.sid

sql mysql Join outer-join

来源：https://stackoverflow.com/questions/62025107/joining-2-or-more-sql-queries-using-outer-join

1条答案

按热度按时间

xzabzqsa1#

使用条件聚合：

select s.id as sid, s.Name as sname,
       sum(case when date(o.created_at) = '2020-05-16' then o.Quantity end) as sum1,
       sum(case when date(o.created_at) = '2020-05-17' then o.Quantity end) as sum2,
       sum(case when date(o.created_at) = '2020-05-18' then o.Quantity end) as sum3,
       sum(case when date(o.created_at) = '2020-05-19' then o.Quantity end) as sum4,
       sum(case when date(o.created_at) = '2020-05-20' then o.Quantity end) as sum5
from orders o inner join
     stock s
     on o.Stock_id = s.id 
group by s.id;

这要简单得多，不需要挑剔 join s或子查询。此外，它还修正了日期算法。

赞(0）回复(0）举报 2021-07-26

我来回答

使用外部连接连接2个或更多sql查询

1条答案

相关问题

热门标签

最新问答