sql - 将 4 个 SQL 子查询合并为单个查询（ZOHO Analytics）

Question

ZOHO Analytics 是一个支持 MySQL、SQL server、Oracle、PostgreSQL 等 SQL 语法的数据库...

我正在使用 MySQL 的语法。

我能够构建整个 SQL，它由 4 个级别的子查询组成，但问题就在这里。ZOHO Analytics 只允许 1 级子查询。因此，我最终不得不将完整的 SQL 拆分为 2 个“查看表”。

*注意：双引号的文本是指表名或列名。

这是第一个视图表“销售人员佣金第 1 部分”：

select 
sp."name" as "Sales Person Name",
round(t1."gross sales", 2) as "Gross Sales",
if(t1."Gross Partner Profit" is null, 0.00, round(t1."Gross Partner Profit", 2)) as "Gross Partner Profit",
round(t1."Gross Sales" - if(t1."Gross Partner Profit" is null, 0, t1."Gross Partner Profit"), 2) as "Gross Profit",
round(100 * (t1."Gross Sales" - if(t1."Gross Partner Profit" is null, 0, t1."Gross Partner Profit")) / t1."gross sales", 2) as "Gross Profit Percentage"
from
(
select
i."sales person id",
sum(ii."total (bcy)") as "gross sales",
sum(item."partner price" * ii."quantity") as "Gross Partner Profit"
from
"invoice items (zoho finance)" ii 
inner join "invoices (zoho finance)" i on i."invoice id"=ii."invoice id"
left join "items (zoho finance)" item on item."item id"=ii."item id"
where
i."invoice date">='2021-07-01' and i."invoice date"<='2021-07-31'
group by i."sales person id"
) as t1
inner join "sales persons (zoho finance)" sp on t1."i.sales person id"=sp."sales person id";

这是第二个视图表“销售人员佣金第 1 部分”：

select t1.*,
round(t1."Commission Percentage" * t1."Gross Sales" / 100, 2) as "Commission Total"
from
(
select *,
case 
when "Gross Profit Percentage" <= 5 then 0
when "Gross Profit Percentage" > 5 and "Gross Profit Percentage" <= 10 then 1.9
when "Gross Profit Percentage" > 10 and "Gross Profit Percentage" <= 15 then 2.9
when "Gross Profit Percentage" > 15 and "Gross Profit Percentage" <= 20 then 3.9
when "Gross Profit Percentage" > 20 and "Gross Profit Percentage" <= 25 then 4.9
when "Gross Profit Percentage" > 25 and "Gross Profit Percentage" <=30 then 5.9
when "Gross Profit Percentage" > 30 then 7.9
end as "Commission Percentage"
from "Sales Person Commission Part 1"
) as t1;

是否有可能将这两个部分重构为单个 SQL，最多只有 1 个子查询？

================================

更新

问题解决了！感谢@shadow 提供提示。这是最终重构的 SQL：

select t1.*,
round(t1."Commission Percentage" * t1."Gross Sales" / 100, 2) as "Commission Total"
from
(
select
sp."name" as "Sales Person",
round(sum(ii."total (bcy)"), 2) as "Gross Sales",
round(if (item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) as "Gross Partner Profit",
round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) as "Gross Profit",
round(100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) , 2) as "Gross Profit Percentage",
case
when item."partner price" is null then 7.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 5 then 0
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 5 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 10 then 1.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 10 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 15 then 2.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 15 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 20 then 3.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 20 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 25 then 4.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 25 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <=30 then 5.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 30 then 7.9
end as "Commission Percentage"
from
"invoice items (zoho finance)" ii 
inner join "invoices (zoho finance)" i on i."invoice id"=ii."invoice id"
inner join "sales persons (zoho finance)" sp on sp."sales person id" = i."sales person id"
left join "items (zoho finance)" item on item."item id"=ii."item id"
where
i."invoice date">='2021-07-01' and i."invoice date"<='2021-07-31'
group by i."sales person id",sp."name", item."partner price"
order by sp."name"
) as t1;

==========================

更新 2

如果字段为空，则替换为coalesce(field,value)

select t1.*,
round(t1."Commission Percentage" * t1."Gross Sales" / 100, 2) as "Commission Total"
from
(
select
sp."name" as "Sales Person",
round(sum(ii."total (bcy)"), 2) as "Gross Sales",
round(coalesce(sum(item."partner price" * ii."quantity"), 0)) as "Gross Partner Profit",
round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2) as "Gross Profit",
round(100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) , 2) as "Gross Profit Percentage",
case
when item."partner price" is null then 7.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 5 then 0
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 5 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 10 then 1.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 10 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 15 then 2.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 15 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 20 then 3.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 20 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 25 then 4.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 25 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <=30 then 5.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 30 then 7.9
end as "Commission Percentage"
from
"invoice items (zoho finance)" ii 
inner join "invoices (zoho finance)" i on i."invoice id"=ii."invoice id"
inner join "sales persons (zoho finance)" sp on sp."sales person id" = i."sales person id"
left join "items (zoho finance)" item on item."item id"=ii."item id"
where
i."invoice date">='2021-07-01' and i."invoice date"<='2021-07-31'
group by i."sales person id",sp."name", item."partner price"
) as t1 order by t1."Sales Person"

标签： sqlzohobooks