如何删除每组较小的记录？

Question

我的表 price 具有当前架构：

item date         shift     price1  price2
1    20110723     day       40      50
1    20110723     night     42      52
1    20110723     weekend   42      52
2    20110723     Night     40      50
...

并且我想保留表中每个项目具有最高价格 1 或价格 2 的记录，即从表中删除具有较小价格 1 或价格 2 的记录。 sql怎么写？

我使用sybase ASE 12.5

Answer 1

在 SQL Server（可能还有其他一些 RDBMS）中，您可以使用 CTE和排名，如下所示：

WITH ranked AS (
  SELECT
    *,
    price1rank = RANK() OVER (PARTITION BY item ORDER BY price1 DESC),
    price2rank = RANK() OVER (PARTITION BY item ORDER BY price2 DESC)
  FROM Test1
)
DELETE FROM ranked
WHERE price1rank <> 1
  AND price2rank <> 1;

---

更新

发布整个测试脚本，包括上面的 DELETE 语句，以便任何愿意的人都可以使用它或只是验证它是否有效：

CREATE TABLE Test1 (
  item int,
  date date,
  shift varchar(30),
  price1 money,
  price2 money
);
GO
INSERT INTO Test1 (item, date, shift, price1, price2)
SELECT 1, '20110723', 'day    ', 40, 50 UNION ALL
SELECT 1, '20110723', 'night  ', 42, 52 UNION ALL
SELECT 1, '20110723', 'weekend', 42, 52 UNION ALL
SELECT 2, '20110723', 'Night  ', 40, 50;
GO
SELECT * FROM Test1
GO
WITH Test1Ranked AS (
  SELECT
    *,
    price1rank = RANK() OVER (PARTITION BY item ORDER BY price1 DESC),
    price2rank = RANK() OVER (PARTITION BY item ORDER BY price2 DESC)
  FROM Test1
)
DELETE FROM Test1Ranked
WHERE price1rank <> 1
  AND price2rank <> 1;
GO
SELECT * FROM Test1
GO
DROP TABLE Test1
GO

Answer 2

如果你在 oracle 中工作，你可以编写以下查询 -

delete from item_table where rowid not in
(
     select rowid from item_table 
     where (item,price1) in (select item,max(price1) from item_table group by item)
        or (item,price2) in (select item,max(price2) from item_table group by item)
)

我听说 sql server 或 mysql 中没有 rowid ...
请告诉我们您正在使用的数据库名称。

你也可以写如下..

delete from item_table where (item,date,shift,price1,price2 ) not in
    (
        select item,date,shift,price1,price2  from item_table 
        where (item,price1) in (select item,max(price1) from item_table group by item)
           or (item,price2) in (select item,max(price2) from item_table group by item)
    )