sql中的产品价格比较

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我有一个类似于下面查询的表格，我每天在此表格中添加产品价格，具有不同的卖家名称:

创建表Product_Price(身份证号码，日期，卖家名称 varchar(20),产品 varchar(10),价格金钱)插入 Product_Price 值 (1, '2012-01-16','Sears','AA', 32)插入 Product_Price 值 (2, '2012-01-16','Amazon', 'AA', 40)插入 Product_Price 值 (3, '2012-01-16','eBay','AA', 27)插入 Product_Price 值 (4, '2012-01-17','Sears','BC', 33.2)插入 Product_Price 值 (5, '2012-01-17','Amazon', 'BC',30)插入 Product_Price 值 (6, '2012-01-17','eBay', 'BC',51.4)插入 Product_Price 值 (7, '2012-01-18','Sears','DE', 13.5)插入 Product_Price 值 (8, '2012-01-18','Amazon','DE', 11.1)插入 Product_Price 值 (9, '2012-01-18', 'eBay','DE', 9.4)

我想要 n 个卖家的结果(随着更多卖家添加到表格中)

<前>DT PRODUCT Sears[我的网站] Amazon Ebay 最低价1/16/2012 AA 32 40 27 易趣1/17/2012 BC 33.2 30 51.4 亚马逊1/18/2012 DE 7.5 11.1 9.4 西尔斯

解决方案

我认为这就是您要找的.

SQLFiddle

这有点难看，但这里有一个小故障.

此块允许您获取值的动态列表.(不记得我从谁那里偷了这个，但它很棒.没有这个，pivot 真的不比一个大的案例陈述方法更好.)

DECLARE @cols AS VARCHAR(MAX)声明 @query AS NVARCHAR(MAX)选择 @cols = STUFF((选择不同的 ',' +QUOTENAME(卖家名称)来自产品_价格FOR XML PATH(''), 类型).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

你的@cols 变量是这样输出的:

[亚马逊]、[eBay]、[西尔斯]

然后您需要构建整个查询的字符串:

select @query ='选择 piv1.*, tt.sellername from (选择 *从(选择 dt, product, SellerName, sum(price) 作为价格从 product_price 组中按 dt, product, SellerName) t1枢轴 (sum(price) for SellerName in (' + @cols + ')) as bob) piv1内部联接(选择 t2.dt,t2.sellername,t1.min_price 从(选择 dt, min(price) as min_price from product_price group by dt) t1内连接(选择 dt,sellername, sum(price) as price from product_price group by dt,sellername)t2 on t1.min_price = t2.price)tt在 piv1.dt = tt.dt'

piv1 派生表为您提供枢轴值.巧妙命名的 tt 派生表为您提供每天销售额最低的卖家.(告诉你它有点丑.)

最后，您运行查询:

execute(@query)

你会得到:

 DT 产品亚马逊 EBAY SEARS 卖家名称2012-01-16 AA 40 27 32 易趣2012-01-17 BC 30 51.4 33.2 亚马逊2012-01-18 DE 11.1 9.4 13.5 易趣

(抱歉，不能把那个位对齐).

我认为如果你有一个可以做交叉表的报告工具，那么在那里做会容易得多.

I have a table looks like given below query, I add products price in this table daily, with different sellers name :

create table Product_Price
    (
      id int,
      dt date,
      SellerName varchar(20),
      Product varchar(10),
      Price money
    )

    insert into Product_Price values (1, '2012-01-16','Sears','AA', 32)
    insert into Product_Price values (2, '2012-01-16','Amazon', 'AA', 40)
    insert into Product_Price values (3, '2012-01-16','eBay','AA', 27)

    insert into Product_Price values (4, '2012-01-17','Sears','BC', 33.2)
    insert into Product_Price values (5, '2012-01-17','Amazon', 'BC',30)
    insert into Product_Price values (6, '2012-01-17','eBay', 'BC',51.4)

    insert into Product_Price values (7, '2012-01-18','Sears','DE', 13.5)
    insert into Product_Price values (8, '2012-01-18','Amazon','DE', 11.1)
    insert into Product_Price values (9, '2012-01-18', 'eBay','DE', 9.4)

I want result like this for n number of sellers(As more sellers added in table)

DT           PRODUCT   Sears[My Site]   Amazon   Ebay   Lowest Price
1/16/2012    AA        32               40       27     Ebay
1/17/2012    BC        33.2             30       51.4   Amazon
1/18/2012    DE        7.5              11.1     9.4    Sears

解决方案

I think this is what you're looking for.

SQLFiddle

It's kind of ugly, but here's a little breakdown.

This block allows you to get a dynamic list of your values. (Can't remember who I stole this from, but it's awesome. Without this, pivot really isn't any better than a big giant case statement approach to this.)

DECLARE @cols AS VARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(SellerName)
                      FROM Product_Price
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '')

Your @cols variable comes out like so:

[Amazon],[eBay],[Sears]

Then you need to build a string of your entire query:

select @query = 
'select piv1.*, tt.sellername from (
select *
from
(select dt, product, SellerName,  sum(price) as price from product_price group by  dt, product, SellerName) t1

pivot (sum(price) for SellerName in (' + @cols + '))as bob
) piv1
inner join
(select t2.dt,t2.sellername,t1.min_price from
(select dt,  min(price) as min_price  from product_price group by  dt) t1
inner join (select dt,sellername, sum(price) as price from product_price group by dt,sellername) t2 on t1.min_price = t2.price) tt
on piv1.dt = tt.dt
'

The piv1 derived table gets you the pivoted values. The cleverly named tt derived table gets you the seller who has the minimum sales for each day. (Told you it was kind of ugly.)

And finally, you run your query:

execute(@query)

And you get:

 DT     PRODUCT     AMAZON  EBAY    SEARS   SELLERNAME
2012-01-16  AA  40  27  32  eBay
2012-01-17  BC  30  51.4    33.2    Amazon
2012-01-18  DE  11.1    9.4     13.5    eBay

(sorry, can't make that bit line up).

I would think that if you have a reporting tool that can do crosstabs, this would be a heck of a lot easier to do there.

相关文章