我的查询:
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。
我的查询:
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。
这就是窗口函数 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
从你的示例中看来,可以合理地假设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;
我使用以下模式来解决这些查询:
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
表上扫描仅第一项。
row_number()
是什么吗?它对分组中的行进行枚举(由partition by
子句定义)。排序基于order by
子句。通过选择值为1,每个分组只选择一行,这将是具有最大日期的行。 - Gordon LinoffINNER JOIN (SELECT DISTINCT h.siteName FROM history h) ON ...
这个怎么样?在我的情况下,它产生了相同的结果。 - Ivan