您有一个看起来像这样的数据集
我需要编写查询以返回每个人选择的第一种和最后一种颜色。 这是我的代码
Select t1.name,t1.color,min(t1.rnkmin),t2.color,max(t2.rnkmax)
From(
Select name,color,Danse_rank() over(partition by name order by time asc) as rnkmin
From table 3) as t1 inner join (
Select name,Danse_rank() over (partition by name order by date asc) as rnkmax
From table 3) as t2 on t1.name=t2.name
但可惜我遇到错误，我不明白为什么 谢谢您的帮助：）
您可以两次使用row_number()：
select color,name,time
from (
select
t.*,row_number() over(partition by name order by time) rn_asc,row_number() over(partition by name order by time desc) rn_desc
from mytable t
) t
where rn_asc = 1 or rn_desc = 1
如果您希望两种颜色都在同一记录中，则可以进行汇总：
select
name,max(case when rn_asc = 1 then color end) as first_color
max(case when rn_desc = 1 then color end) as last_color
from (
select
t.*,row_number() over(partition by name order by time desc) rn_desc
from mytable t
) t
where rn_asc = 1 or rn_desc = 1
group by name
在许多数据库中，最快的方法将使用相关子查询：
select t.*
from t
where t.time = (select min(t2.time)
from t t2
where t2.name = t.name
) or
t.time = (select max(t2.time)
from t t2
where t2.name = t.name
) ;
您要在(name,time)上建立索引。