mysql - 如何在 View 中加入多个子查询

Question

我有以下简化 View 。你可以看到我在同一个表上有多个子查询。有没有一种方法可以将其作为一个子查询？像这样:

(select count(*), sum(..), max(..) from XY1 .....) as (Count1, Sum1, Max1),
(select count(*), sum(..), max(..) from XY2 .....) as (Count2, Sum2, Max2),

这是代码:

drop view if exists view_OffersStat;
create view view_OffersStat as

select
o.Id as Offer_Id,
o.Title,
o.Start,
o.Expires,

(select count(*) from Sale as s where s.Merchant_Id=o.Merchant_Id) as Total_Sales,
(select sum(s.Net) from Sale as s where s.Merchant_Id=o.Merchant_Id) as Total_Net,
(select sum(s.Gross)) from Sale as s where s.Merchant_Id=o.Merchant_Id) as Total_Gross,
(select max(s.Gross) from Sale as s where s.Merchant_Id=l.Merchant_Id) as Max_Gross,

from Offer as o;

Answer 1

drop view if exists view_OffersStat;
create view view_OffersStat as

select
o.Id as Offer_Id,
o.Title,
o.Start,
o.Expires,
count(*) as Total_Sales,
sum(s.Net) as Total_Net,
sum(s.Gross) as Total_Gross,
max(s.Gross) as Max_Gross,

from Offer as o
inner join Sale as s
on s.Merchant_Id=o.Merchant_Id
group by s.Merchant_Id;