sql中多个商店的价格比较

create table Products
    (
      id int,
      ProductName varchar(200),
      ProductCategory varchar(200),
      ProductImage varchar(200),
      ProductUri varchar(200),
    )

Insert into Products values(135, 'Product X', 'Digital Camera', Null, Null)
Insert into Products values(136, 'Product Y', 'Mobile', Null, Null)

create table Product_Price
    (
      id int,
      ProductId int,
      dt date,
      SellerName varchar(20),
      Available varchar(20),
      Offer varchar(20),      
      Price money,
      Shipping money
    )

insert into Product_Price values (1, 135,'2012-01-16','Sears','In Stock','30% discount',32.00,2.00)
insert into Product_Price values (2, 135,'2012-01-16','Amazon','In Stock',Null,30.00,NULL)
insert into Product_Price values (3, 135,'2012-01-16','eBay','Just 2 Left',Null,28.00,1.00)

insert into Product_Price values (4, 136,'2012-01-16','Sears','In Stock','30% discount',30.00,6.00)
insert into Product_Price values (5, 136,'2012-01-16','Amazon','In Stock',Null,28.00,4.00)
insert into Product_Price values (6, 136,'2012-01-16','eBay','Out Of stock',Null,Null,Null)

我想要这样的结果：

   ID   ProductName ProductCategory ProductImage    ProductUri  SearsTotal Price(Price+Shipping)    SearsAvailablity    SearsOffer  #Competitors    DifferencePercentage(Sears & others)    AmazonTotal Price(Price+Shipping)   AmazonAvailablity   AmazonOffer eBayTotal Price(Price+Shipping) eBayAvailablity eBayOffer
135 Product X   Digital Camera  NULL    NULL    34  In Stock    30% discount    2   15.25423729 30  In Stock    NULL    29  Just 2 Left NULL
136 Product Y   Mobile  NULL    NULL    36  In Stock    25% discount    1   12.5    32  In Stock    NULL    NULL    Out Of stock    NULL
  

第 1 步在这里：SQL中的产品价格比较 https://stackoverflow.com/questions/19841022/product-price-comparison-in-sql/19842699我的测试在这里：http://sqlfiddle.com/#!3/ec1e7/6 http://sqlfiddle.com/#!3/ec1e7/6

尽管我不确定您想要如何计算 DifferencePercentage 列，但我对此进行了尝试，因此您可能需要仔细查看。

CREATE TABLE #tempProduct
(
    ID INT,
    SellerName VARCHAR(100),
    Total MONEY,
    Availability VARCHAR(100),
    Offer VARCHAR(100),
    Competitors INT
)

INSERT INTO #tempProduct (ID, SellerName, Total, Availability, Offer)
SELECT DISTINCT p.id, pp.SellerName, pp.Price + ISNULL(pp.Shipping,0), pp.Available, pp.Offer
FROM Products p
JOIN Product_Price pp
    ON p.id = pp.ProductId

-- Get Sears competitors
UPDATE tp
SET Competitors = pp.CompetitorCount
FROM #tempProduct tp
JOIN (
        SELECT ProductId, COUNT(sellerName) [CompetitorCount] 
        FROM Product_Price 
        WHERE SellerName <> 'Sears' AND Price + ISNULL(Shipping,0) IS NOT NULL
        GROUP BY ProductId
    ) pp
    ON pp.ProductId = tp.ID
WHERE tp.SellerName = 'Sears'

 SELECT DISTINCT 
    p.id, 
    p.ProductName,
    p.ProductCategory, 
    p.ProductImage, 
    p.ProductUri, 
    stp.Total [SearsTotal], 
    stp.Availability [SearsAvailability], 
    stp.Offer [SearsOffer], 
    stp.Competitors [#Competitors],
    100 - (((ISNULL(etp.Total,0) + ISNULL(atp.Total, 0))/stp.Competitors)/stp.Total) * 100 [DifferencePercentage(Sears & others)], -- Not sure how you want to calculate price difference
    atp.Total, 
    atp.Availability [AmazonTotal], 
    atp.Offer [AmazonOffer], 
    etp.Total [eBayTotal], 
    etp.Availability [eBayAvailability], 
    etp.Offer [eBayOffer]
 FROM Products p
JOIN Product_Price pp
    ON pp.ProductId = p.ID
JOIN #tempProduct stp
    ON stp.ID = p.id
JOIN #tempProduct etp
    ON etp.ID = p.id
JOIN #tempProduct atp
    ON atp.ID = p.id
WHERE stp.SellerName = 'Sears'  
AND etp.SellerName = 'eBay'
AND atp.SellerName = 'Amazon'