基于表格中某一列删除重复值

Question

我的查询：

SELECT sites.siteName, sites.siteIP, history.date
FROM sites INNER JOIN
     history ON sites.siteName = history.siteName
ORDER BY siteName,date

输出结果的第一部分：

如何在siteName列中删除重复项？我只想保留基于 date 列更新后的行。

在上面的示例输出中，我需要的行是1、3、6、10。

- Ned

Answer 1

这就是窗口函数 row_number() 发挥作用的地方：

SELECT s.siteName, s.siteIP, h.date
FROM sites s INNER JOIN
     (select h.*, row_number() over (partition by siteName order by date desc) as seqnum
      from history h
     ) h
    ON s.siteName = h.siteName and seqnum = 1
ORDER BY s.siteName, h.date

- Gordon Linoff

---

能否稍微解释一下这个查询？ - JacksOnF1re

你知道 row_number() 是什么吗？它对分组中的行进行枚举（由 partition by 子句定义）。排序基于 order by 子句。通过选择值为1，每个分组只选择一行，这将是具有最大日期的行。 - Gordon Linoff

哇，Gordon 用了2分钟想出这个。摇头。 - steve-o

steve-o，你是在暗示他立刻看到了这篇帖子哈哈。可能只花了一分钟或更短的时间。非常令人印象深刻。 - ehtio

INNER JOIN (SELECT DISTINCT h.siteName FROM history h) ON ... 这个怎么样？在我的情况下，它产生了相同的结果。 - Ivan

Answer 2

从你的示例中看来，可以合理地假设siteIP列是由siteName列确定的（也就是说，每个站点只有一个siteIP）。如果是这样的话，那么使用group by就有一个简单的解决方案：

select
  sites.siteName,
  sites.siteIP,
  max(history.date)
from sites
inner join history on
  sites.siteName=history.siteName
group by
  sites.siteName,
  sites.siteIP
order by
  sites.siteName;

然而，如果我的假设是错误的（即站点可能有多个siteIP），那么从你的问题中无法确定你想要在第二列返回哪个siteIP。 如果只是任意的siteIP，则以下查询将可行：

select
  sites.siteName,
  min(sites.siteIP),
  max(history.date)
from sites
inner join history on
  sites.siteName=history.siteName
group by
  sites.siteName
order by
  sites.siteName;

- Mikhail Makarov

Answer 3

我使用以下模式来解决这些查询：

SELECT *
FROM t
WHERE t.field=(
  SELECT MAX(t.field) 
  FROM t AS t0 
  WHERE t.group_column1=t0.group_column1
    AND t.group_column2=t0.group_column2 ...)

这意味着它将选择字段值为其最大值的记录。为了将其应用于您的查询，我使用了公共表达式，以便不必重复两次JOIN：

WITH site_history AS (
  SELECT sites.siteName, sites.siteIP, history.date
  FROM sites
  JOIN history USING (siteName)
)
SELECT *
FROM site_history h
WHERE date=(
  SELECT MAX(date) 
  FROM site_history h0 
  WHERE h.siteName=h0.siteName)
ORDER BY siteName

需要注意的是，仅当我们计算最大值的字段是唯一的时才有效。在您的示例中，date字段应该对于每个siteName是唯一的，也就是说，如果IP不能在毫秒内多次更改，则为唯一。根据我的经验，通常情况下都是这样，否则您无法确定哪条记录是最新的。如果history表具有(site，date)的唯一索引，则此查询也非常快速，可以使用索引范围扫描在history表上扫描仅第一项。

- Oliv