价格与sql中的多个商店进行比较 [英] Price compare with multiple store in sql
问题描述
创建表产品(身份证号码，产品名称 varchar(200),产品类别 varchar(200),ProductImage varchar(200),产品Uri varchar(200),)插入到产品值中(135、'Product X'、'Digital Camera'、Null、Null)插入到产品值中(136、'Product Y'、'Mobile'、Null、Null)创建表 Product_Price(身份证号码，产品 ID 整数，日期，卖家名称 varchar(20),可用 varchar(20),提供 varchar(20),价格金钱，运费)插入 Product_Price 值 (1, 135,'2012-01-16','Sears','In Stock','30% discount',32.00,2.00)插入 Product_Price 值 (2, 135,'2012-01-16','Amazon','In Stock',Null,30.00,NULL)插入 Product_Price 值 (3, 135,'2012-01-16','eBay','Just 2 Left',Null,28.00,1.00)插入 Product_Price 值 (4, 136,'2012-01-16','Sears','In Stock','30% discount',30.00,6.00)插入 Product_Price 值 (5, 136,'2012-01-16','Amazon','In Stock',Null,28.00,4.00)插入 Product_Price 值(6, 136,'2012-01-16','eBay','Out Of stock',Null,Null,Null)我想要这样的结果:
<前>ID ProductName ProductCategory ProductImage ProductUri SearsTotal Price(Price+Shipping) SearsAvilablity SearsOffer #Competitors DifferencePercentage(Sears & others) AmazonTotal Price(Price+Shipping) AmazonAvailability AmazonOffer eBayTotal Price(Price+Shipping) eBayAvilablity eBayOffer135 产品 X 数码相机 NULL NULL 34 In Stock 30% discount 2 15.25423729 30 In Stock NULL 29 Just 2 Left NULL136 Product Y Mobile NULL NULL 36 In Stock 25% discount 1 12.5 32 In Stock NULL NULL Out of stock NULL步骤 1 在这里:sql 中的产品价格比较我的测试在这里:http://sqlfiddle.com/#!3/ec1e7/6
好吧，这是可能的"，但它变得非常疯狂.不要在家里尝试这个！你真的需要某种报告工具来做这种事情.
基于我们在另一篇文章中使用的逻辑，我们还需要一个变量.它将构建一个字符串来选择我们每个旋转列的最大值.所以我们将开始:
DECLARE @cols AS VARCHAR(MAX)声明 @cols2 为 VARCHAR(MAX)声明 @query AS NVARCHAR(MAX)声明 @COL_ALIASES 为 VARCHAR(MAX)选择@COL_ALIASES = ISNULL(@COL_ALIASES + ', ', '') +'Max(' + QUOTENAME(SellerName + '_TOTAL') + ') As ' + QUOTENAME(SellerName + '_TOTAL') + ', ' +'Max(' + QUOTENAME(SellerName + '_AVAILABLE') + ') As ' + QUOTENAME(SellerName + '_AVAILABLE')来自#Product_Price选择 @cols = STUFF((选择不同的 ',' +QUOTENAME(SellerName + '_TOTAL')来自#Product_PriceFOR XML PATH(''), 类型).value('.', 'NVARCHAR(MAX)'), 1, 1, '')选择 @cols2 = STUFF((选择不同的 ',' +QUOTENAME(SellerName + '_AVAILABLE')来自#Product_PriceFOR XML PATH(''), 类型).value('.', 'NVARCHAR(MAX)'), 1, 1, '')然后我们从所有有趣的东西中组合查询字符串:
select @query =' 选择 p.id ,p.productname,' + + @COL_ALIASES + CHAR(10) +' 来自产品 p内部联接 (选择产品编号，' + @cols + ',' + @cols2 + '从(选择p1.id 作为产品 ID，p2.sellername + ''' + '_TOTAL' + ''' 作为 TotalSeller，p2.sellername + ''' + '_AVAILABLE' + ''' 作为 AvailableSeller，p2.价格，p2.available从产品 p1内连接 product_price p2在 p1.id = p2.productid ) t1PIVOT (max(price) for TotalSeller in (' + @cols + ')) tPIVOT (max(available) for AvailableSeller in (' + @cols2 + ') ) u )列兵ON p.id = pvt.productidGROUP BY PID,p.产品名称'最后，我们运行它:
Exec sp_executesql @Query您只需继续扩展此逻辑即可添加其他支点.你可以明白为什么我说这变得疯狂了.我这样做只是因为我有点喜欢挑战，但我当然不建议尝试实际使用它.正如人们在其他帖子中指出的那样，您可能会将自己暴露在 SQL 注入中，这将是一个难以维护的野兽.如果我从现在起 6 个月之后再看这个，我可能不知道这个查询到底发生了什么.
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)
And i want result like this :
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
Step 1 is here : Product price comparison in sql My test is here : http://sqlfiddle.com/#!3/ec1e7/6
Well, this is "possible", but it's getting pretty crazy. Don't try this at home! You really need some sort of reporting tool to do this kind of thing.
Building on the logic we used in the other post, we'll need one more variable. It's going to build a string that selects the max of each of our pivoted columns. So we'll start with:
DECLARE @cols AS VARCHAR(MAX)
DECLARE @cols2 AS VARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
DECLARE @COL_ALIASES AS VARCHAR(MAX)
Select @COL_ALIASES = ISNULL(@COL_ALIASES + ', ', '') +
'Max(' + QUOTENAME(SellerName + '_TOTAL') + ') As ' + QUOTENAME(SellerName + '_TOTAL') + ', ' +
'Max(' + QUOTENAME(SellerName + '_AVAILABLE') + ') As ' + QUOTENAME(SellerName + '_AVAILABLE')
from #Product_Price
select @cols = STUFF((SELECT distinct ',' +
QUOTENAME(SellerName + '_TOTAL')
FROM #Product_Price
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '')
select @cols2 = STUFF((SELECT distinct ',' +
QUOTENAME(SellerName + '_AVAILABLE')
FROM #Product_Price
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '')
Then we assemble the query string from all of that fun stuff:
select @query =
' select p.id ,
p.productname,' + + @COL_ALIASES + CHAR(10) +
' from Products p
inner join (
select
productId,
' + @cols + ',' + @cols2 + '
from
(
select
p1.id as ProductID,
p2.sellername + ''' + '_TOTAL' + ''' As TotalSeller,
p2.sellername + ''' + '_AVAILABLE' + ''' as AvailableSeller,
p2.price,
p2.available
from
products p1
inner join product_price p2
on p1.id = p2.productid ) t1
PIVOT (max(price) for TotalSeller in (' + @cols + ')) t
PIVOT (max(available) for AvailableSeller in (' + @cols2 + ') ) u )
pvt
ON p.id = pvt.productid
GROUP BY p.id,
p.productname
'
And finally, we run it:
Exec sp_executesql @Query
You would just keep expanding on this logic to add your additional pivots. You can see why I said this is getting crazy. I did this much just because I kind of enjoyed the challenge, but I certainly wouldn't recommend trying to actually use this. As folks have pointed out in other posts, you could expose yourself to SQL injection, and it would be a beast to maintain. If I looked at this 6 months from now, I'd probably have no idea what the heck was going on in this query.
这篇关于价格与sql中的多个商店进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
