sql - 如何连接两个表并针对客户品牌获取完整数据？

Question

我有两张表，在一张表中我有完整的产品详细信息（品牌和产品名称），在另一张表中我有截至日期月份的销售详细信息。

在第三列中，我想根据客户品牌明智地了解所有产品的详细信息。

例如：

我正在销售 4 种不同型号的三星手机，有客户只从我这里购买了两种型号。所以我也有可能向他出售同一品牌的其他产品。

我附上了我有表 1 和表 2 的图像，我需要表 3 中的数据

我试过完全外连接

with cte as
(select Sales_Manager_ProductAnalysisTable.Customer, ProductMasterfromSales.Brand,ProductMasterfromSales.product
from ProductMasterfromSales
cross join Sales_Manager_ProductAnalysisTable
group by Sales_Manager_ProductAnalysisTable.Customer, ProductMasterfromSales.Brand,ProductMasterfromSales.product
)
select cte.Customer,cte.Brand, cte.product,
   case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.jan end as jan,
    case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as feb,
     case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Mar,
      case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Apr,
       case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as May,
        case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Jun,
         case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Jul,
          case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Aug,
           case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Sep,
            case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Oct,
             case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Nov,
              case when Sales_Manager_ProductAnalysisTable.product is null or Sales_Manager_ProductAnalysisTable.Brand is null then 0 else Sales_Manager_ProductAnalysisTable.feb end as Dev
from cte
left join Sales_Manager_ProductAnalysisTable on Sales_Manager_ProductAnalysisTable.Customer = cte.Customer and Sales_Manager_ProductAnalysisTable.Brand = cte.Brand and Sales_Manager_ProductAnalysisTable.product = cte.product; 


+---------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+------+------+
| Brand   | Customer            | Product  | Jan  | Feb  | Mar  | Apr  | May  | Jun  | Jul  | Aug  | Sep  | Oct  | Nov  | Dec  |
+---------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+------+------+
| Samsung | viaCom Technologies | Note 10  | NULL | 1980 | NULL | NULL | NULL | 2640 | NULL | NULL | NULL | NULL | NULL | NULL |
+---------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+------+------+
| Samsung | viaCom Technologies | Note 9   | NULL | 0    | 360  | NULL | NULL | NULL | -92  | -8   | NULL | NULL | NULL | NULL |
+---------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+------+------+
| Samsung | viaCom Technologies | Iphone 8 | NULL | NULL | NULL | NULL | NULL | 100  | NULL | NULL | NULL | NULL | NULL | NULL |
+---------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+------+------+

标签： sqlsql-server

Answer 1

使用 . 生成所有客户/产品组合cross join。然后引入存在的数据。

实际上，您并不想要所有组合，因为您只是在寻找客户拥有的品牌。join继续做你brand想要的笛卡尔积。

尝试：

select cb.customer, cb.brand, pms.product,
       coalesce(smpa.jan, 0) as jan,
       coalesce(smpa.feb, 0) as feb,
       . . . 
from (select distinct smpa.customer, smpa.brand
      from Sales_Manager_ProductAnalysisTable smpa
     ) cb join
     ProductMasterfromSales pms
     on pms.brand = smpa.brand left join
     Sales_Manager_ProductAnalysisTable smpa
     on smpa.customer = cb.customer and
        smpa.brand = cb.brand and
        smpa.product = pms.product