来自连接表sq的行总和

2024-05-19 • 问答

我有一个查询，要求从这样的不同视图中加入结果。

select ip.product_no,ip.description,ac.description,op.operation_no,op.sale_qty,ip.contract,ro.work_center_n,ipp.mul_order_qty as "Qty/box"

from product ip
join accounting_group ac on ip.acc_group = ac.acc_group
join routing_operation ro on ip.product_no= ro.product_noand ip.contract = ro.contract
join INVEN_PLANNING ipp on ip.product_no= ipp.product_noand ip.contract = ipp.contract
join OPER_STATISTIC op on ip.product_no= op.product_no
where ro.center_no like '%'
and ip.product_no like '%'

这个查询给我这样的结果：

product_no = desc == ac.des = operation_no = sale_qty= contract = work_center_no = "Qty/box"  
12121        clothes   finish     1           20             US           7               15
12121        clothes   finish     2           10             US           7               15
12123        clothes   finish     1           20             US           7               15
12123        clothes   finish     2           20             US           7               15
12124        clothes   finish     1           20             US           7               15
12124        clothes   finish     2           20             US           7               15
12125        clothes   finish     3           20             US           7               15

那我该如何总结report_qty？预期的结果将是这样：

 product_no = desc == ac.des  = SUM(sale_qty)= contract = work_center_no = "Qty/box"  
 12121      clothes   finish         40           US           7             15
 12123      clothes   finish         40           US           7             15

您似乎正在寻找聚合：

select 
    ip.product_no,ip.description,ac.description,sum(op.sale_qty) sum_sale_qty,ip.contract,ipp.mul_order_qty as "Qty/box"
from product ip
join accounting_group ac on ip.acc_group = ac.acc_group
join routing_operation ro on ip.product_no= ro.product_no and ip.contract = ro.contract
join INVEN_PLANNING ipp on ip.product_no= ipp.product_no and ip.contract = ipp.contract
join OPER_STATISTIC op on ip.product_no= op.product_no
where ro.center_no like '%' and ip.product_no like '%'
group by 
    ip.product_no,ipp.mul_order_qty

注意：where子句有点奇怪：

where ro.center_no like '%' and ip.product_no like '%'

这可以按以下方式进行优化（这将允许使用索引）：

where ro.center_no is not null and ip.product_no is not null

此外，由于ip.product_no是在inner join条件下使用的，因此我们知道它不为null，因此可以归结为：

where ro.center_no is not null

来自连接表sq的行总和

tanghui22222 回答：来自连接表sq的行总和

大家都在问