擅长:python、mysql、java
<p>您可以使用<code>exists</code>检查同一天是否存在另一条记录,该记录的值是否大于两倍,并使用窗口功能每天对顶部记录进行筛选:</p>
<pre><code>select *
from (
select
t.*,
row_number() over(partition by date order by time) rn
from mytable t
where exists (
select 1 from mytable t1 where t1.date = t.date and t1.value = 2 * t.value
)
) t
where rn = 1
</code></pre>
<p>在<code>row_number()</code>不可用的SQLite版本中,另一个选项是使用相关子查询进行筛选:</p>
<pre><code>select t.*
from mytable t
where
exists(select 1 from mytable t1 where t1.date = t.date and t1.value = 2 * t.value)
and t.time = (select min(t1.time) from mytable t1 where t1.date = t.date)
</code></pre>