I have a large set of data that needs to be updated based on price. Very simply, the price of an item in Code/Size 1 must be equal to or less than the Code/Size 2. Code/Size 2 must be equal to or less than Code/Size 3, and so on:
我有一大堆需要根据价格更新的数据。非常简单,代码/大小1中的项目的价格必须等于或小于代码/大小2.代码/大小2必须等于或小于代码/大小3,依此类推:
Item Code Size Price
-------------------------------
66227019 OTH 1 $466
66227019 OTH 2 $486
66227019 OTH 3 $476
66227019 OTH 4 $537
66227019 IVC 1 $458
66227019 IVC 2 $428
66227019 IVC 3 $458
66227019 IVC 4 $458
Must look like:
必须看起来像:
Item Code Size Price
-------------------------------
66227019 OTH 1 $466
66227019 OTH 2 $486
66227019 OTH 3 $486
66227019 OTH 4 $537
66227019 IVC 1 $458
66227019 IVC 2 $458
66227019 IVC 3 $458
66227019 IVC 4 $458
I've tried some self-joins and I'm basically losing my mind. Any help is sincerely appreciated.
我尝试了一些自我加入,我基本上都在失去理智。任何帮助都是真诚的感谢。
Thanks!
谢谢!
4 个解决方案
#1
3
For SQL-Server 2012, you can use this:
对于SQL-Server 2012,您可以使用:
; WITH cte AS
( SELECT *,
MaxPrice = MAX(Price)
OVER (PARTITION BY Item, Code
ORDER BY Size
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
)
FROM Products
)
UPDATE cte
SET Price = MaxPrice
WHERE Price < MaxPrice ;
Tested at SQL-Fiddle
在SQL-Fiddle测试
#2
1
Does this get you what you need?
这能满足您的需求吗?
UPDATE LaterRec
SET Price = PrevRec.Price
FROM Data AS LaterRec
INNER JOIN Data AS PrevRec ON LaterRec.Item = PrevRec.Item AND LaterRec.Code = PrevRec.Code AND PrevRec.Size < LaterRec.Size
WHERE
PrevRec.Price > LaterRec.Price
-- Disregard this entry if there's a record *between* PrevRec and LaterRec
AND NOT EXISTS (SELECT * FROM Data AS Intrmdt WHERE LaterRec.Item = Intrmdt.Item AND LaterRec.Code = Intrmdt.Code AND Intrmdt.Size > PrevRec.Size AND Intrmdt.Size < LaterRec.Size)
#3
1
answer is Recursive CTE. not depends on the SQL SERVER version.
答案是递归CTE。不依赖于SQL SERVER版本。
DECLARE @t TABLE
(
item INT
,code sysname
,SIZE INT
,price INT
)
INSERT INTO @t( item, code, SIZE, price )
SELECT 1,'oth',1,466
UNION ALL SELECT 1,'oth',2,486
UNION ALL SELECT 1,'oth',3,476
UNION ALL SELECT 1,'oth',4,537
UNION ALL SELECT 1,'ivc',1,458
UNION ALL SELECT 1,'ivc',2,428
UNION ALL SELECT 1,'ivc',3,458
UNION ALL SELECT 1,'ivc',4,458
SELECT * FROM @t
;WITH cte1 (item,code,size,price,rownum1,rownum2)
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY item,code ORDER BY item,code,SIZE) AS rownum1, ROW_NUMBER() OVER (ORDER BY item,code,SIZE) AS rownum2
FROM @t
),
cte2 (item,code,size,price,rownum1,rownum2)
AS
(
SELECT *
FROM cte1 t1
WHERE rownum2=1
UNION ALL
SELECT t1.item,t1.code,t1.size
,CASE WHEN t1.price>=t2.price THEN t1.price
WHEN t1.price<t2.price THEN t2.price
END AS proce
,t1.rownum1,t1.rownum2
FROM cte2 t2
JOIN cte1 t1
ON t2.rownum2+1=t1.rownum2
)
SELECT item,code,size,price FROM cte2
#4
0
Supposing t
is your table's name:
假设t是你的表名:
update t set price=p
from (
select nxt.item i,nxt.code c,nxt.size s,max(cur.price) p
from t cur join t nxt
on (cur.item=nxt.item and cur.code=nxt.code
and cur.size<nxt.size and cur.price>nxt.price)
group by nxt.item,nxt.code,nxt.size
) a
where item=i and code=c and size=s
#1
3
For SQL-Server 2012, you can use this:
对于SQL-Server 2012,您可以使用:
; WITH cte AS
( SELECT *,
MaxPrice = MAX(Price)
OVER (PARTITION BY Item, Code
ORDER BY Size
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
)
FROM Products
)
UPDATE cte
SET Price = MaxPrice
WHERE Price < MaxPrice ;
Tested at SQL-Fiddle
在SQL-Fiddle测试
#2
1
Does this get you what you need?
这能满足您的需求吗?
UPDATE LaterRec
SET Price = PrevRec.Price
FROM Data AS LaterRec
INNER JOIN Data AS PrevRec ON LaterRec.Item = PrevRec.Item AND LaterRec.Code = PrevRec.Code AND PrevRec.Size < LaterRec.Size
WHERE
PrevRec.Price > LaterRec.Price
-- Disregard this entry if there's a record *between* PrevRec and LaterRec
AND NOT EXISTS (SELECT * FROM Data AS Intrmdt WHERE LaterRec.Item = Intrmdt.Item AND LaterRec.Code = Intrmdt.Code AND Intrmdt.Size > PrevRec.Size AND Intrmdt.Size < LaterRec.Size)
#3
1
answer is Recursive CTE. not depends on the SQL SERVER version.
答案是递归CTE。不依赖于SQL SERVER版本。
DECLARE @t TABLE
(
item INT
,code sysname
,SIZE INT
,price INT
)
INSERT INTO @t( item, code, SIZE, price )
SELECT 1,'oth',1,466
UNION ALL SELECT 1,'oth',2,486
UNION ALL SELECT 1,'oth',3,476
UNION ALL SELECT 1,'oth',4,537
UNION ALL SELECT 1,'ivc',1,458
UNION ALL SELECT 1,'ivc',2,428
UNION ALL SELECT 1,'ivc',3,458
UNION ALL SELECT 1,'ivc',4,458
SELECT * FROM @t
;WITH cte1 (item,code,size,price,rownum1,rownum2)
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY item,code ORDER BY item,code,SIZE) AS rownum1, ROW_NUMBER() OVER (ORDER BY item,code,SIZE) AS rownum2
FROM @t
),
cte2 (item,code,size,price,rownum1,rownum2)
AS
(
SELECT *
FROM cte1 t1
WHERE rownum2=1
UNION ALL
SELECT t1.item,t1.code,t1.size
,CASE WHEN t1.price>=t2.price THEN t1.price
WHEN t1.price<t2.price THEN t2.price
END AS proce
,t1.rownum1,t1.rownum2
FROM cte2 t2
JOIN cte1 t1
ON t2.rownum2+1=t1.rownum2
)
SELECT item,code,size,price FROM cte2
#4
0
Supposing t
is your table's name:
假设t是你的表名:
update t set price=p
from (
select nxt.item i,nxt.code c,nxt.size s,max(cur.price) p
from t cur join t nxt
on (cur.item=nxt.item and cur.code=nxt.code
and cur.size<nxt.size and cur.price>nxt.price)
group by nxt.item,nxt.code,nxt.size
) a
where item=i and code=c and size=s