时间戳

首页 > 解决方案 > 每个零件号和零件号的销售总和在另一个表中有其替代零件号

sql-server - 每个零件号和零件号的销售总和在另一个表中有其替代零件号

问题描述

我有两张表，每个月都有零件号和销售量。另一个表有零件编号的替代部分。

表格1

Partnumber     jun19sale   jul19sale
A                 1                1
B                 2                1
C                 3                4
E                 5                3
D                 1                2

表2

Partnumber     subpart   
A                 B               
A                 C               
A                 D

我怎么能得到这样的东西。

Partnumber     jun19sale   jul19sale
A                  7               8
B                  7               8
C                  7               8
E                  5               3
D                  7               8

我尝试使用 or 的子查询，其中给出了准确的结果，但这需要太多时间。因为表有大量数据。

标签： sql-server

解决方案

很长的路要走

使用左连接（销售中的某些记录不会关联）将销售编号连接到零件（将每个销售与子零件关联），如果零件零件编号存在，则对零件编号进行分组和求和，如果不存在，则对销售零件编号进行分组和求和t（销售额以子部分和主要部分表示，因此我们希望将销售额中的一些子部分映射到主要部分）。一旦我们的销售额仅表示为主要部分，将其左连接（否则您将不会在输出中获得 E 行）到主要部分映射到主要部分和子部分的部分列表（否则您不会在输出中获取 A 行）

    SELECT 
      COALESCE(parts.partnumber, sales.partnumber) partnumber,
      sum(jun19sale) as jun19sum,
      sum(jul19sale) as jul19sum
    FROM
      table1 sales
      LEFT JOIN
      table2 parts 
      ON
        sales.partnumber = parts.subpart
    GROUP BY COALESCE(parts.partnumber, sales.partnumber)

这将给出类似等的总计A, 7, 8。现在我们需要将其加入到部分到子部分的映射中，该子部分还包括映射到主要部分（作为子部分）的主要部分，如下所示：

SELECT
  COALESCE(msparts.subpart, subsum.partnumber) as partnumber,
  subsum.jun19sum,
  subsum.jul19sum
FROM
  (
    SELECT DISTINCT partnumber, partnumber as subpart FROM table1
    UNION ALL
    SELECT partnumber, subpart FROM table1
  ) msparts
  RIGHT JOIN
  (
    SELECT 
      COALESCE(parts.partnumber, sales.partnumber) partnumber,
      sum(jun19sale) as jun19sum,
      sum(jul19sale) as jul19sum
    FROM
      table1 sales
      LEFT JOIN
      table2 parts 
      ON
        sales.partnumber = parts.subpart
    GROUP BY COALESCE(parts.partnumber, sales.partnumber)
  ) subsum
  ON
    msparts.partnumber = subsum.partnumber

不过，我们需要一个技巧，以防止 A 行丢失，因为部件表将 a 映射到 b、c、d 而不是 a - 这意味着如果我们加入 sims 并显示子部件，A 行将从结果。如果我们添加一英寸将 A 映射到 A 以及 BC 和 D 的假行，那么该行将保留。这就是 UNION ALL 位的作用

短途

使用分析/窗口函数来做同样的事情可能更简单；

SELECT
  sales.partnumber,
  SUM(jun19sale) OVER(PARTITION BY COALESCE(parts.partnumber, sales.partnumber)) jun19sale,
  SUM(jul19sale) OVER(PARTITION BY COALESCE(parts.partnumber, sales.partnumber)) jul19sale
FROM
  table1 sales
  LEFT JOIN
  table2 parts
  ON sales.partnumber = parts.subpart

在这里，我们使用 sales 表作为驱动程序，因此我们默认保留 A 行和 E 行。我们仍然在部件表上进行左连接，因此 BCD 等一些部件映射到 A。我们要求分析对来自部件的主要部件组求和，或者如果它为空，则来自销售的主要部件（这是 PARTITION BY )

COALESCE 是 IFNULL 的跨平台兼容版本

推荐阅读