Question

我有一个这样的表:

timestamp                value           person
===============================================
2010-01-12 00:00:00       33              emp1
2010-01-12 11:00:00       22              emp1
2010-01-12 09:00:00       16              emp2
2010-01-12 08:00:00       16              emp2
2010-01-12 12:12:00       45              emp3
2010-01-12 13:44:00       64              emp4
2010-01-12 06:00:00       33              emp1
2010-01-12 15:00:00       12              emp5

我想找到与每个人相关的最高价值。显而易见的问题是:

select person,max(value) from table group by person

现在,我想列入与每个最高点(价值)相关的时间段。我无法在上述询问中使用时间段一栏,因为众所周知,这个栏按条款在小组中获胜。因此,我写道:

select x.timestamp,x.value,x.person from table as x,
(select person,max(value) as maxvalue from table group by person order by maxvalue 
 desc) as y
where x.person = y.person
and x.value = y.maxvalue

This works -- to an extent. I now see:

timestamp                value           person
===============================================
2010-01-12 13:44:00       64              emp4
2010-01-12 12:12:00       45              emp3
2010-01-12 06:00:00       33              emp1
2010-01-12 00:00:00       33              emp1
2010-01-12 08:00:00       16              emp2
2010-01-12 09:00:00       16              emp2
2010-01-12 15:00:00       12              emp5

现在,问题在于,我从头一和时二的所有条目来看都达到了同样的最高点。

我只想看到最新时间。我想:

timestamp                value           person
===============================================
2010-01-12 13:44:00       64              emp4
2010-01-12 12:12:00       45              emp3
2010-01-12 06:00:00       33              emp1
2010-01-12 09:00:00       16              emp2
2010-01-12 15:00:00       12              emp5

What kind of query would I have to write? Is it possible to extend the nested query I wrote to achieve what I want or does one have to rewrite everything from the scratch?

如果它很重要,因为我正在使用Sqlite,那么,时间灯实际上被储存为陪审日。我利用时间(日期)职能,把他们重新转化为每一次调查中的严格代表性。

Answer 1

你们几乎在那里:

SELECT max(x.timestamp) AS timestamp, x.value, x.person
     , y.max_value, y.ct_value, y.avg_value
FROM   table AS x
JOIN  (
    SELECT person
         , max(value) as max_value
         , count(value) as ct_value
         , avg(value) as avg_value
    FROM   table
    GROUP  BY person
    ) AS y ON (x.person, x.value) = (y.person, y.max_value)
GROUP BY x.person, x.value, y.max_value, y.ct_value, y.avg_value
-- ORDER  BY x.person, x.value

不能将<代码>max(x.timestamp)与同样的nes格中计算,因为你不希望每人的绝对最高值,而是随附最高值。因此,您不得不在下一个问答阶段再加一时间。

Compute max(x.timestamp) before you convert it to its string representation - though your format would sort correctly, too. But that that should perform better.

请注意,我如何改变你与[内部]的条件和(简化)加入的条件。同样,更像标准灵活、更可读。

所有这一切可以在一个定点一级进行,其功能为:“条形”、“条形”和“条形”——第1条——价值(+/条)”——这些功能在所有大的建筑工程中实施,但并非在结构上实施。

Edit

在征求意见后增加的合计数。

Edit

友情链接