作者paranoia5201 (社会黑暗、行路难)
标题Re: [SQL ] 如何统计连续的状态?
时间Wed Apr 15 13:28:29 2020
借用强者的测试资料也玩一下。
我的写法：
http://sqlfiddle.com/#!9/4bdc57/14
我的理解是，原PO想要直接看到学生连续 grade > 60 的天数，分数，以及名单。
所以以下会取出这样的资料：
select sid, grade, date
from (
select
a.sid,
a.grade,
a.date,
ifnull(c.date, convert(a.date + 1, date)) next_date,
today_flag,
ifnull(prev_flag, 0) prev_flag,
ifnull(next_flag, 0) next_flag,
today_flag + ifnull(prev_flag, 0) + ifnull(next_flag, 0) mark
from (
select *, if(grade > 60, 1, 0) today_flag
from sdb
) a
left join
(
select *, if(grade > 60, 1, 0) prev_flag
from sdb
) b
on a.sid = b.sid and a.date = b.date + 1
left join
(
select *, if(grade > 60, 1, 0) next_flag
from sdb
) c
on a.sid = c.sid and a.date = c.date - 1
where today_flag + ifnull(prev_flag, 0) + ifnull(next_flag, 0) >= 2
and a.grade > 60
order by a.sid, a.date
) d
※ 引述《cutekid (可爱小孩子)》之铭言：
: 网址: http://sqlfiddle.com/#!9/4bdc57/10
: #测资
: create table sdb(sid char(1),grade int,date char(8));
: insert into sdb values
: ('A',20,'20200401'),('A',48,'20200402'),('A',65,'20200403'),
: ('A',49,'20200404'),('A',71,'20200405'),('A',56,'20200406'),
: ('A',61,'20200407'),('A',55,'20200408'),('A',72,'20200409'),
: ('A',68,'20200410'),
: ('B',61,'20200401'),('B',61,'20200402'),('B',61,'20200403'),
: ('B',61,'20200404'),('B',61,'20200405'),('B',61,'20200406'),
: ('B',61,'20200407'),('B',61,'20200408'),('B',72,'20200409'),
: ('B',68,'20200410')
: #查询结果
: select sid,count(*) - max(if(grade<=60,num,0)) as ans
: from (
: select
: sid,grade,
: if(@sid = sid,@num:=@num+1,@num:=1) as num,
: @sid := sid
: from (
: select * from sdb order by sid,date
: ) t1,(select @sid,@num) t2
: ) result
: group by sid
: ※ 引述《jami520 (ALEN)》之铭言：
: : （针对 SQL 语言的问题，用这个标题。请用 Ctrl+Y 砍掉这行）
: : 资料库名称：mysql
: : 资料库版本：5.0
: : 内容/问题描述：目前有个学生资料表 sdb
: : pid sid(学生代码) grade(分数) gdate(日期)
: : 我想要列出到最近一天，每位学生分数>60分的连续天数
: : 假设有位学生这十天(远->近)，分数如下
: : 20, 48, 65, 49, 71, 56, 61, 55, 72, 68
: : 这样这位学生就是2天(72与68)
: : 而我想要用一段SQL语法去把每位学生这十天的情况都列出来
: : 不晓得要怎样来写呢? 谢谢
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 18.194.116.40 (美国)
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1586928511.A.6BC.html
1F：推 cutekid: 嗯，我们对题目的理解与定义不同，所以解法与结果也不同 04/15 14:39