SQL -行排序
SQL -行排序
提问于 2014-07-01 04:09:57
我有一个玩桌子的日子，date_played，胜利者，失败者，下面的价值观，
(Jun-03-14, USA, China)
(Jun-05-14, USA, Russia)
(Jun-06-14, France, Germany)
.
.
.
.
(Jun-09-14, USA, Russia)我需要获得所有的例子，其中美国已经赢得了准确的3排在一个序列。
我尝试使用以下查询。
Select
date, winner, loser,
RANK() OVER (PARTITION BY winner ORDER BY date rows 2 preceding) as rank
from playday;回答 3
Stack Overflow用户
回答已采纳
发布于 2015-08-12 05:37:24
您可以使用以下查询。
select winner,loser,date,cnt from (select winner, loser, date, date - lag(date,3) over ( order by date) as cnt from playday) where cnt >=3Stack Overflow用户
发布于 2014-07-01 04:27:14
首先你得弄清楚他们最后一次失败是什么时候。第二，数赢的次数，大于(>)最后一次输的日期。第三，如果计数> 3，则返回大于上次损失的所有行。
对不起，我面前没有一个SQL解析器来正确地将它放入代码中。
Set @team_name = "USA";
select date, winner, loser
from playday
where (select count(*) as wins_since_loss from playday
where playday.winner = @team_name
and playday.date >
(select max(date) as losing_date from playday where playday.loser = @team_name)) = 3Stack Overflow用户
发布于 2015-12-16 12:04:01
查询的目的是提取美国队连续3次获胜的行序列，而不是少或多(我用日期作为date1)。
select date1, winner, loser from
(
select count (*) over (partition by change) as id, date1,winner,loser from
(
select date1,winner,loser,lag_loser, sum(case when loser <> lag_loser and (loser='USA' or lag_loser='USA') then 1 else 0 end) over (order by date1 rows unbounded preceding) as change from
(
select date1, winner,loser, lag(loser) over (order by date1) as lag_loser from
(
select date1, winner, loser from playday
where winner ='USA' or loser = 'USA'
ORDER BY date1 ASC
)
)
)
)
where winner ='USA' and id =3页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24502321
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号