首先,建议使用 INNER JOIN 来连接两个表,而不是使用逗号分隔的 FROM 子句。这会使 SQL 语句更易于阅读和理解。
其次,可以使用视图(View)来简化 SQL 语句,将复杂的逻辑转移到视图中,从而使 SQL 语句更简洁和易读。
此外,对于 SUM 函数的多次调用,可以使用 WITH 语句或者临时表(Temporary Table)来避免多次计算,从而提高 SQL 查询的性能。
最后,建议使用索引来优化查询,特别是在大型数据集的情况下,这可以显著提高查询性能。
下面是优化后的 SQL 语句:
WITH ssb_summary AS (
SELECT partner_id,
SUM(actual_amount) AS 销售额,
SUM(refund_amount) AS 退货金额,
SUM(refund_good_cost) AS 退货成本,
SUM(refund_good_cost)*0.4 AS 退货损耗,
SUM(cooperation_fee) AS 合作费用,
SUM(point_fee) AS 扣点费用,
SUM(receivable) AS 应收款,
(SUM(amount)-SUM(point_fee))*0.06 AS 提成金额,
SUM(settlement_amount) AS 结算金额,
(SUM(amount)-SUM(point_fee)-SUM(refund_amount))-(SUM(cost)-SUM(refund_good_cost))-SUM(refund_good_cost)*0.4-SUM(cooperation_fee)-(SUM(amount)-SUM(point_fee))*0.06 AS 毛利润,
((SUM(amount)-SUM(point_fee)-SUM(refund_amount))-(SUM(cost)-SUM(refund_good_cost))-SUM(refund_good_cost)*0.4-SUM(cooperation_fee)-(SUM(amount)-SUM(point_fee))*0.06)/(SUM(amount)-SUM(refund_amount)-SUM(point_fee)) AS 毛利率
FROM saas_bill
WHERE bill_month BETWEEN '2023-06' AND '2023-07'
GROUP BY partner_id
)
SELECT sp.company_name,
ssb_summary.销售额,
ssb_summary.退货金额,
ssb_summary.退货成本,
ssb_summary.退货损耗,
ssb_summary.合作费用,
ssb_summary.扣点费用,
ssb_summary.应收款,
ssb_summary.提成金额,
ssb_summary.结算金额,
ssb_summary.毛利润,
ssb_summary.毛利率,
sp.cooperate_status,
sp.id,
SUM(wipe_zero)
FROM saas_partner sp
INNER JOIN ssb_summary ON sp.id = ssb_summary.partner_id
WHERE sp.company_type = '门店' AND sp.cooperate_status='合作中'
GROUP BY sp.id, sp.company_name, ssb_summary.销售额, ssb_summary.退货金额, ssb_summary.退货成本, ssb_summary.退货损耗, ssb_summary.合作费用, ssb_summary.扣点费用, ssb_summary.应收款, ssb_summary.提成金额, ssb_summary.结算金额, ssb_summary.毛利润, ssb_summary.毛利率
ORDER BY ssb_summary.毛利润 DESC
这样,SQL 查询就会更快,更易于维护和理解。