--逐行累加小计
--“UNBOUNDED PRECEDING”无界前驱 在 SQL 中指的是窗口函数中的一种特殊值，表示窗口的起始位置是数据集的最开始位置。
select a.id,a._id,a.rq,a.mmsl,a.mmje,b.id,b.slb.price 行金额
,sum(b.slb.price) over(order by b.id rows between unbounded preceding and current row) 累计金额
from m_cpjh a join d_cpjh b on a._id=b.mainid
--其中rows between unbounded preceding and current row 是显式写法，可以省略

--按分组小计
--方案1，传统方案，性能最佳。尤其是在大数据量、多字段汇总、关联多个字表时更显优势
select a.id,a._id,a.rq,a.mmsl,a.mmje
,sum(b.slb.price) 按单号分组合计金额
from m_cpjh a join d_cpjh b on a._id=b.mainid
group by a.id,a._id,a.rq,a.mmsl,a.mmje
--方案2：性能差，大数据量不推荐。不新增额外小计行，同一组每一行后面都挂着该组总金额
select a.id,a._id,a.rq,a.mmsl,a.mmje
,sum(b.slb.price) over(partition by b.mainid) 按单号分组合计金额
from m_cpjh a join d_cpjh b on a._id=b.mainid
--方案3：ROLLUP 分组小计。适合：按字段分组，每组最后自动多出一行合计
--效果：正常明细行保留，每组末尾自动多出一行：分类小计，最后还会出一个全局总计，只保留「每组小计」，不要全局总计
--最后部分加 HAVING 过滤：
select a.id,a._id,a.rq
,SUM(b.sl*b.price) AS 按单号分组合计金额
from m_cpjh a join d_cpjh b on a._id=b.mainid
GROUP BY a.id,a._id,a.rq WITH ROLLUP
HAVING GROUPING(a.id)=0