使用自连接和聚合进行更新

发布于 2024-10-31 09:37:13 字数 477 浏览 6 评论 0原文

我正在尝试使用自联接和聚合来更新表。

例如，一个表具有以下列：

商店、商品、价格、低价、低价商店

我需要填充 lowprice 和 lowprice store。

lowprice 将是：

Select item,min(price) group by item

lowprice store 将是每个商品具有 min(price) 的商店。

我也希望能够更进一步。假设两家商店以相同的低价提供商品。那么lowprice store 的值将是“store a/store b”，但这部分不太重要。

我正在使用 SQL Server。

原文

I'm trying to update a table using a self join and aggregates.

For example, a table has the following columns:

store, item, price, lowprice, lowprice store

I need to populate lowprice and lowprice store.

The lowprice would be:

Select item,min(price) group by item

The lowprice store would be the store with the min(price) for each item.

I'd love to be able to take it a step further as well. Say two stores offer an item at the same lowprice. Then the value for lowprice store would be 'store a/store b' but this part is less important.

I'm using SQL Server.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无尽的现实 2024-11-07 09:37:13

我同意 @JNK 的评论，即您最好使用 VIEW。对于 SQL Server 2005+，您可以尝试：

CREATE VIEW LowPrices 
AS

SELECT A.store, A.item, A.price, A.Low_Price, MIN(B.store) Low_Price_Store
FROM (  SELECT  *, MIN(price) OVER(PARTITION BY item) Low_Price
    FROM YourTable) A
JOIN YourTable B
ON A.Low_Price = B.price
GROUP BY A.store, A.item, A.price, A.Low_Price

如果您仍想执行 UPDATE，请尝试：

WITH CTE AS
(
    SELECT A.store, A.item, A.price, A.Low_Price, MIN(B.store) Low_Price_Store
    FROM (  SELECT  *, MIN(price) OVER(PARTITION BY item) Low_Price
            FROM YourTable) A
    JOIN YourTable B
    ON A.Low_Price = B.price
    GROUP BY A.store, A.item, A.price, A.Low_Price
)

UPDATE A
SET A.LowPrice = B.Low_Price,
    A.LowPriceStore = B.Low_Price_Store
FROM YourTable A
JOIN CTE B
ON A.store = B.Store AND A.item = B.item

I agree with @JNK comment that you are better off using a VIEW. For SQL Server 2005+ you can try:

CREATE VIEW LowPrices 
AS

SELECT A.store, A.item, A.price, A.Low_Price, MIN(B.store) Low_Price_Store
FROM (  SELECT  *, MIN(price) OVER(PARTITION BY item) Low_Price
    FROM YourTable) A
JOIN YourTable B
ON A.Low_Price = B.price
GROUP BY A.store, A.item, A.price, A.Low_Price

If you still want to do the UPDATE, then try:

WITH CTE AS
(
    SELECT A.store, A.item, A.price, A.Low_Price, MIN(B.store) Low_Price_Store
    FROM (  SELECT  *, MIN(price) OVER(PARTITION BY item) Low_Price
            FROM YourTable) A
    JOIN YourTable B
    ON A.Low_Price = B.price
    GROUP BY A.store, A.item, A.price, A.Low_Price
)

UPDATE A
SET A.LowPrice = B.Low_Price,
    A.LowPriceStore = B.Low_Price_Store
FROM YourTable A
JOIN CTE B
ON A.store = B.Store AND A.item = B.item

回复收藏 0 原文

~没有更多了~