关联表的递归自查询(SQL Server)

Given the following structure and data, I am looking for joins by customer where a manufacturer has changed:

鉴于以下的结构和数据，我正在寻找一个制造商发生变化的客户连接:

create table #sale (
    saleid int,
    customerid int,
    productid int,
    saledate date
);
create table #product (
    productId int,
    name varchar(max),
    manufacturerId int
);
create table #manufacturer (
    manufacturerid int,
    name varchar(max)
)

insert into #manufacturer values (1, 'Manufacturer 1');
insert into #manufacturer values (2, 'Manufacturer 2');

insert into #product values (1, 'Product A', 1);
insert into #product values (2, 'Product B', 1);
insert into #product values (3, 'Product C', 2);

insert into #sale values (1, 101, 1, '2013-01-01');
insert into #sale values (2, 101, 2, '2015-04-01');
insert into #sale values (3, 102, 3, '2013-03-01');
insert into #sale values (4, 102, 3, '2015-01-01');
insert into #sale values (5, 103, 1, '2013-01-01');
insert into #sale values (6, 103, 3, '2015-06-01');
insert into #sale values (7, 102, 1, '2015-06-01');

In this scenario, two customers have switched to products with different manufacturers and the ideal results look like this:

在这种情况下，两个客户换了不同厂家的产品，理想的结果是:

customerid  previous manufacturer   new manufacturer    date
102         Manufacturer 2          Manufacturer 1      6/1/2015
103         Manufacturer 1          Manufacturer 2      6/1/2015

I've been trying to use a CTE, but have not found success. Appreciate and insight or guidance.

我一直在尝试使用CTE，但没有成功。欣赏、洞察或指导。

Update - I could live with expanding horizontally a certain number of joins on customerid to show different manufacturers and dates (5-10 joins). That one is a lot easier to do for me than trying to UNION ALL with a CTE.

更新——我可以水平扩展customerid上的一定数量的连接来显示不同的制造商和日期(5-10个连接)。对我来说，这比把所有的都和CTE结合起来要容易得多。

Thanks!

谢谢!

2 个解决方案

#1

Query

WITH X AS 
 (
  SELECT s.*
        ,M.manufacturerid
        ,m.name
        ,ROW_NUMBER() OVER (PARTITION BY s.CustomerID
                            ORDER BY saledate ASC) rn 
  FROM #sale s 
  INNER JOIN #product p      ON s.productid = p.productId
  INNER JOIN #manufacturer m ON p.manufacturerId = m.manufacturerid 
)
SELECT xx.customerid
       ,xy.name        [previous manufacturer]
       ,xx.name        [new manufacturer]
       ,xx.saledate [Date]
FROM x xy
INNER JOIN x xx ON xx.customerid = xy.customerid
               AND xx.rn = xy.rn + 1
               AND xx.manufacturerid <> xy.manufacturerid

Result:

╔════════════╦═══════════════════════╦══════════════════╦════════════╗
║ customerid ║ previous manufacturer ║ new manufacturer ║    Date    ║
╠════════════╬═══════════════════════╬══════════════════╬════════════╣
║        102 ║ Manufacturer 2        ║ Manufacturer 1   ║ 2015-06-01 ║
║        103 ║ Manufacturer 1        ║ Manufacturer 2   ║ 2015-06-01 ║
╚════════════╩═══════════════════════╩══════════════════╩════════════╝

#2

Here is an alternative using LAG:

这里有一个使用滞后的替代方法:

select customerid,
       [previous manufacturer],
       [new manufacturer],
       saledate
from (
  select s.customerid, 
         m.name as [new manufacturer],
         s.saledate,
         lag(m.name) over (partition by s.customerid order by s.saledate) as [previous manufacturer],
         case when 
           lag(m.manufacturerId) over (partition by s.customerid order by s.saledate) is not null 
           and lag(m.manufacturerId) over (partition by s.customerid order by s.saledate) <> m.manufacturerId
         then 1 else 0
         end as is_manufacturer_change
  from sale s
  join product p on p.productid = s.productid
  join manufacturer m on m.manufacturerid = p.manufacturerid) x
where x.is_manufacturer_change = 1
order by customerid, saledate

SQLFiddle

SQLFiddle