作者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/m.aspx?n=bbs/Database/M.1586928511.A.6BC.html
1F：推 cutekid: 嗯，我們對題目的理解與定義不同，所以解法與結果也不同 04/15 14:39