입력:
postgres=> select player_1, row_number() over (order by id) as r1, row_number() over (order by id desc) as r2 from (select id, player_1 from matches order by id limit 5) as foo;
player_1 | r1 | r2
--------------------+----+----
Kafelnikov Yevgeny | 1 | 5
Dreekmann Hendrik | 2 | 4
Courier Jim | 3 | 3
Krajicek Richard | 4 | 2
Rafter Patrick | 5 | 1
(5 rows)
예상 :
first | last
--------------------+----+----
Kafelnikov Yevgeny | Rafter Patrick
나는 다음을 시도했지만 5 행이 있으며 위와 같이 단일 행에 둘 다 필요합니다.
postgres=> select case when r1 = 1 then player_1 end, case when r2 = 1 then player_1 end from (select player_1, row_number() over (order by id) as r1, row_number() over (order by id desc) as r2 from (select id, player_1 from matches order by id limit 5) as foo) as too;
case | case
--------------------+----------------
Kafelnikov Yevgeny |
|
|
|
| Rafter Patrick
(5 rows)
당신은 올바른 길을 가고 있습니다. 다음 행 번호를 사용하여 조건부 집계를 수행 할 수 있습니다.
select
max(player_1) filter(where r1 = 1) first_player,
max(player_1) filter(where r2 = 1) last_player
from (
select player_1,
row_number() over (order by id) as r1,
row_number() over (order by id desc) as r2
from (select * from matches order by id limit 5) t
order by id limit 5
) t;
first_value()및 distinct다음을 사용 하여이 작업을 수행 할 수도 있습니다 .
select distinct
first_value(player_1) over(order by id) first_player,
first_value(player_1) over(order by id desc) last_player
from (select * from matches order by id limit 5) t
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다