问题描述
使用Sqlite,我想获取具有最大时间戳的行集合.该表包含项的属性,即键-值对和时间戳.我想为每个属性选择最新的值.
Using Sqlite, I'd like to fetch the collection of rows each with the greatest timestamp. The table contains the properties of items, which are key-value pairs and timestamp. I'd like to select the most recent value for each property.
请考虑以下简化的架构和数据:
Consider the following simplified schema and data:
CREATE TABLE Properties (thing VARCHAR,
key VARCHAR,
value VARCHAR,
timestamp INT);
INSERT INTO Properties VALUES ("apple", "color", "red", 0);
INSERT INTO Properties VALUES ("apple", "taste", "sweet", 0);
INSERT INTO Properties VALUES ("apple", "size", "small", 0);
INSERT INTO Properties VALUES ("watermelon", "taste", "sweet", 0);
INSERT INTO Properties VALUES ("watermelon", "size", "large", 0);
INSERT INTO Properties VALUES ("watermelon", "color", "pink", 1);
INSERT INTO Properties VALUES ("watermelon", "color", "green", 0);
我想为thing="watermelon"
编写一个查询,该查询返回:
I'd like to write a query for thing="watermelon"
that returns:
taste|sweet
size|large
color|pink
请注意,有两行带有key="color"
,查询将返回具有最大timestamp
值的行.另外,一个属性的最大timestamp
可能与另一属性不同.
Note that there are two rows with key="color"
, and the query returns the row with the greatest timestamp
value. Also, the greatest timestamp
for one property may be different from another property.
到目前为止我尝试过的内容包括:
获取thing="watermelon"
的属性集:
SELECT DISTINCT(key) FROM Properties WHERE thing='watermelon';
获取thing="watermelon"
的最新值key="color"
:
SELECT *
FROM Properties
WHERE thing='watermelon'
AND key='color'
ORDER BY timestamp DESC
LIMIT 1;
但是我不知道如何将两者结合起来.我可能是从命令式编程的角度来解决这个问题的,这就是为什么我希望获得帮助.
But I can't figure out how to combine the two. I'm probably coming at this from an imperative programming perspective, which is why I'd appreciate assistance.
推荐答案
在SQLite 3.7.11或更高版本中,您可以简单地使用MAX()从组中选择一行:
In SQLite 3.7.11 or later, you can simply use MAX() to select one row from a group:
SELECT key, value, MAX(timestamp)
FROM Properties
WHERE thing = 'watermelon'
GROUP BY key;
这篇关于如何选择每个项目具有最大时间戳的行集?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!