在Group之后，SQL仍然有重复项

Question

我试着在我们的两个仓库之间得到一个总计的qty_on_hand，但是在我做完一组之后，就不合并了。我不知道我做错了什么。

SELECT 
    sku_master.warehouse,
     sku_master.sku
   , sku_master.min_on_hand
   , sku_master.max_on_hand
   , x.total_qty_on_hand

FROM [wms].dbo.[sku_master]

left join
(
  SELECT 
     sku_master.sku, sum(location_inventory.qty_on_hand) as total_qty_on_hand
  FROM [wms].[dbo].[location_inventory]
JOIN [wms].dbo.[sku_master] ON location_inventory.sku = sku_master.sku
      GROUP BY sku_master.sku) x on sku_master.sku = x.sku

结果

​

​

预期结果

​

​

Answer 1

看起来您想要每个qty_on_hand的和所有sku。尝试使用OUTER APPLY这样的方法

SELECT m.sku,
       m.min_on_hand,
       m.max_on_hand,
       oa.total_qty_on_hand
FROM   (SELECT sku,
               Min(min_on_hand) min_on_hand,
               Max(max_on_hand) max_on_hand
        FROM   [wms].dbo.[sku_master] m
        GROUP  BY sku) m
       OUTER Apply (SELECT Sum(l.qty_on_hand) AS total_qty_on_hand
                    FROM   [wms].[dbo].[location_inventory] l
                    WHERE  l.sku = m.sku) oa 

Answer 2

如果您只需要一个总计，那么就不要加入，或者只是为示例添加一个联合

  SELECT 
      sku_master.warehouse,
       sku_master.sku
     , sku_master.min_on_hand
     , sku_master.max_on_hand
     , x.total_qty_on_hand

  FROM [wms].dbo.[sku_master]

  left join
  (
    SELECT 
       sku_master.sku, sum(location_inventory.qty_on_hand) as total_qty_on_hand
    FROM [wms].[dbo].[location_inventory]
  JOIN [wms].dbo.[sku_master] ON location_inventory.sku = sku_master.sku
        GROUP BY sku_master.sku) x on sku_master.sku = x.sku
  union 

  SELECT null, null, nullm null, sum(location_inventory.qty_on_hand)
  FROM [wms].[dbo].[location_inventory]
  JOIN [wms].dbo.[sku_master] ON location_inventory.sku = sku_master.sku

或者你没有分组和重复sku_master.warehouse。删除它表单选择

  SELECT 
       sku_master.sku
     , sku_master.min_on_hand
     , sku_master.max_on_hand
     , x.total_qty_on_hand

  FROM [wms].dbo.[sku_master]

  left join
  (
    SELECT 
       sku_master.sku, sum(location_inventory.qty_on_hand) as total_qty_on_hand
    FROM [wms].[dbo].[location_inventory]
  JOIN [wms].dbo.[sku_master] ON location_inventory.sku = sku_master.sku
        GROUP BY sku_master.sku) x on sku_master.sku = x.sku

Answer 3

您可以使用这个简单的查询

 SELECT 
  t.sku, 
  min(t.min_on_hand)  as min_qty_on_Hand,
  max(t.max_on_hand) as max_on_hand,
  sum(t.total_qty_on_hand) + sum(t2.total_qty_on_hand) as total_qty_on_hand
FROM table1 t
inner join table2 t2
 on t.sku = t2.sku
group by t.sku

所附图像显示了包含结果的处理过程。

注意:这里的结果与您在问题中提到的相同，您在这里需要的只是将表名更改为表名。

​

​