时间戳

首页 > 解决方案 > SQL 大幅增加来自多个 JOINS 的总和

java - SQL 大幅增加来自多个 JOINS 的总和

问题描述

我需要在下面的 SQL 语句中加入多个表。与原始语句相比，当我添加这些连接时，总和大大增加了计数：

LEFT outer join pp_sku_pattern skupattern 
ON skupattern.sp_sku_no = item.sku_no

LEFT outer join si_pi_shrinkages_hist pishretail 
ON pish_inventory_date = :inventoryDate
AND
pish_sku_no = item.sku_no
AND
ROWNUM = 1 

Left outer join si_store_cycle_counts cyclecounts 
ON cyclecounts.scc_str_no = :storeNbr
AND
scc_sku = item.sku_no

带有仪表板数据视图的 原始 SQL 语句：带有仪表板数据视图图像的原始 SQL 语句（准确计数）

带有仪表板数据视图 的新 SQL 语句：带有仪表板数据视图图像的新 SQL 语句（计数不准确）

具有正确总和计数的原始 SQL 语句：

SELECT dept.dept_code,
       dept.dept_desc,
       var.sprvh_inventory_date,
       var.sprvh_store,
       var.sprvh_sku,
       brand.br_name,
       item.sku_style,
       SUM(var.sprvh_phys_qty),
       SUM(var.sprvh_book_qty),
       SUM(var.sprvh_curr_variance),
       SUM(var.sprvh_hist_variance),
       SUM(var.sprvh_revision_number)
FROM   si_pi_research_var_hist var
       left outer join pp_stock_keeping_units item
                    ON var.sprvh_sku = item.sku_no
                       AND var.sprvh_company_code = item.sku_company_code
       left outer join pp_departments dept
                    ON dept.dept_company_code = item.sku_company_code
                       AND dept.dept_code = item.sku_dept_code
       left outer join pp_brands brand
                    ON brand.br_id = item.sku_br_id
WHERE  var.sprvh_inventory_date = :inventoryDate
       AND VAR.sprvh_store = :storeNbr
       AND ( ( var.sprvh_phys_qty IS NOT NULL
               AND var.sprvh_phys_qty <> 0 )
              OR ( var.sprvh_book_qty IS NOT NULL
                   AND var.sprvh_book_qty <> 0 )
              OR ( var.sprvh_curr_variance IS NOT NULL
                   AND var.sprvh_curr_variance <> 0 )
              OR ( var.sprvh_hist_variance IS NOT NULL
                   AND var.sprvh_hist_variance <> 0 ) )
GROUP  BY dept.dept_code,
          dept.dept_desc,
          var.sprvh_inventory_date,
          var.sprvh_store,
          var.sprvh_sku,
          brand.br_name,
          item.sku_style
HAVING SUM(var.sprvh_curr_variance) <> 0
        OR SUM(var.sprvh_hist_variance) <> 0
ORDER  BY Abs(SUM(var.sprvh_curr_variance))

产生错误总和计数的全新 SQL 语句：

SELECT dept.dept_code,
       dept.dept_desc,
       var.sprvh_inventory_date,
       var.sprvh_store,
       var.sprvh_sku,
       brand.br_name,
       item.sku_style,
       skupattern.sp_pattern,
       pishretail.pish_retail,
       cyclecounts.scc_reason_process_flag,
       SUM(var.sprvh_phys_qty),
       SUM(var.sprvh_book_qty),
       SUM(var.sprvh_curr_variance),
       SUM(var.sprvh_hist_variance),
       SUM(var.sprvh_revision_number)
FROM   si_pi_research_var_hist var
       left outer join pp_stock_keeping_units item
                    ON var.sprvh_sku = item.sku_no
                       AND var.sprvh_company_code = item.sku_company_code
       left outer join pp_departments dept
                    ON dept.dept_company_code = item.sku_company_code
                       AND dept.dept_code = item.sku_dept_code
       left outer join pp_brands brand
                    ON brand.br_id = item.sku_br_id
       left outer join pp_sku_pattern skupattern
                    ON skupattern.sp_sku_no = item.sku_no
       left outer join si_pi_shrinkages_hist pishretail
                    ON pishretail.pish_inventory_date = :inventoryDate
                       AND pish_sku_no = item.sku_no
                       AND ROWNUM = 1
       left outer join si_store_cycle_counts cyclecounts
                    ON cyclecounts.scc_str_no = :storeNbr
                       AND scc_sku = item.sku_no
WHERE  var.sprvh_inventory_date = :inventoryDate
       AND VAR.sprvh_store = :storeNbr
       AND ( ( var.sprvh_phys_qty IS NOT NULL
               AND var.sprvh_phys_qty <> 0 )
              OR ( var.sprvh_book_qty IS NOT NULL
                   AND var.sprvh_book_qty <> 0 )
              OR ( var.sprvh_curr_variance IS NOT NULL
                   AND var.sprvh_curr_variance <> 0 )
              OR ( var.sprvh_hist_variance IS NOT NULL
                   AND var.sprvh_hist_variance <> 0 ) )
GROUP  BY dept.dept_code,
          dept.dept_desc,
          var.sprvh_inventory_date,
          var.sprvh_store,
          var.sprvh_sku,
          brand.br_name,
          item.sku_style,
          skupattern.sp_pattern,
          pishretail.pish_retail,
          cyclecounts.scc_reason_process_flag
HAVING SUM(var.sprvh_curr_variance) <> 0
        OR SUM(var.sprvh_hist_variance) <> 0
ORDER  BY Abs(SUM(var.sprvh_curr_variance))

问题：如何在新的 SQL 语句中保持原始语句总和与 3 个新连接相同？

标签： javasqloraclejoin

解决方案

您的问题来自您用于连接的非唯一值。例如：

 left outer join si_store_cycle_counts cyclecounts
                    ON cyclecounts.scc_str_no = :storeNbr
                       AND scc_sku = item.sku_no

作为第一。尝试为每个表字段使用表别名。

 left outer join si_store_cycle_counts cyclecounts
                    ON cyclecounts.scc_str_no = :storeNbr
                       AND cyclecounts.scc_sku = item.sku_no

第二。检查每个连接条件。举个例子。如果加入从 si_pi_research_var_hist 中返回多于一行的连接表中的一行，则它的位置进行修复。

推荐阅读