如何获得2列组合的最后记录？

编程入门行业动态更新时间:2024-10-27 01:29:00

本文介绍了如何获得2列组合的最后记录？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我认为可以与CamelCamelCamel，Keepa等服务进行比较。可以说，我跟踪了两个国家/地区每天的商品价格。所以我的桌子叫趋势，看起来像这样

I have a situation which I think can be compared to services like CamelCamelCamel, Keepa and so on. Lets say I track the price of an article on each day for a couple of countries. So my table, lets call it Trend, would look something like this

Id Created ArticleId Country Price ------------------------------------------------- 01 19/11/05 452 US 45.90 02 19/11/05 452 CA 52.99 03 19/11/05 452 MX 99.99 04 19/11/06 452 US 20.00 05 19/11/06 452 CA 25.00 06 19/11/06 452 MX 50.00 ... 97 19/11/05 738 US 12.99 98 19/11/05 738 CA 17.50 99 19/11/05 738 MX 45.50

所以是第二天，我想更新 Trend 表。如果某个国家/地区的价格仍然相同，则跳过商品/国家/地区组合。如果有新价格，我将添加一条新记录。

So it's the next day and I want to update the Trend table. If the price in a country is still the same, I skip the article/country combination. If there is a new price I'll add a new record.

现在，我想查询表格以获取每个 ArticleId / 国家组合。但是只有它的最后一条记录（按时间戳排序）。因此，以上述示例为例，我希望获得记录 04 ， 05 和 06 表示 ArticleId 452 。不是 01 ， 02 和 03

Now I want to query the table to get each ArticleId / Country combination. But only the last record of it (orderd by timestamp). So taken the example above I'd expect to get the records 04, 05 and 06 for ArticleId 452. Not 01, 02 and 03

所以我从这个基本查询开始。但是，如何更改它以得到预期的结果？

So I start out with this basic query. But how do I get to change it to get my expected results?

SELECT * FROM Trend ORDER BY Created DESC

推荐答案

一种方法使用相关子查询进行过滤：

One method uses a correlated subquery for filtering:

select t.* from trend t where t.created = ( select max(t1.created) from trend t1 where t1.articleId = t.articleId and t1.country = t.country )

为了提高性能，您需要在上建立索引（articleId，国家/地区，已创建）。

For performance, you want an index on (articleId, country, created).

您可能还需要考虑采用反左联接方法：

You might also want to consider the anti-left join approach:

select t.* from trend t left join trend t1 on t1.articleId = t.articleId and t1.country = t.country and t1.created > t.created where t1.articleId is null

最后，另一个典型的解决方案是加入带有汇总查询的表：

Finally, another typical solution is to join the table with an aggregate query:

select t.* from trend t inner join ( select articleId, country, max(created) created from trend group by articleId, country ) t1 on t1.articleId = t.articleId and t1.country = t.country and t1.created = t.created

解决方案的效果取决于数据的大小和分布。

Which solution performs better depends on the size and distribution of your data.