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

Question

我有两张表，首先有零件号和每个月的销售额。另一张表有零件号的替代零件。

表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的子查询，在其中可以得到准确的结果，但这需要太多时间。因为表有大量的数据。

Answer 1

长途

使用左连接将销售编号连接到零件(将每个销售与子零件关联((销售中的一些记录不会关联(，如果零件编号存在，则对其进行分组和求和；如果没有，则对销售零件编号进行分组和加和(销售以子零件和主零件表示，因此我们希望将销售中的某些子零件映射到主零件(。一旦我们的销售额仅表示为主要零件，请左键连接(否则您将不会在输出中获得行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，也映射到B、C和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。我们仍然在零件表上进行左联接，因此一些零件(如B C D(被映射到a。我们要求分析人员对零件的主要零件组求和，或者如果它为空，则对销售的主要零件求和(这是PARTITION BY(

COALESCE是IFNULL 的跨平台兼容版本