顯示的資料表內容
$student=
"select redo_date
,case when course2__redos.redo_time=1 then'上午'
when course2__redos.redo_time=2 then '下午'
else null END as redo_time
,case when course2__redos.redo_admission_state=1 then'路跑$s'
when course2__redos.redo_admission_state=0 then '游泳$s'
else null END as redo_admission_state
,case when course2__courses.c_place=null then'缺席'
else c_place END as c_place
from course2__redos
left join course2__choices
on course2__redos.redo_place = course2__choices.ch_id
left join course2__courses
on course2__choices.ch_c_id = course2__courses.c_id
where redo_stuid='$redo_stuid' ";
目的是想讓$s抓取順序的數字，讓資料能顯示這筆資料是第幾筆資料.....
'路跑$s'和'游泳$s'能
EX:路跑1(路跑第一位)、路跑2(路跑第二位)、....、游泳1(游泳第一位)
php必須顯示出第一位申請 第二位申請等
算出照同一天(redo_date)(日期)
的同一時段(redo_time)(1=上午、2=下午)
同一類行申請的(redo_admission_state)(1=路跑、0=游泳)
第$s位
依redo_admission(申請時間)排出先後順序
想不通該怎樣能讓$s顯示順位....
應該說要怎麼在條件上同時指定三個欄位相同的資料......
如果只有一個欄位還好...三個不知道怎麼設定
設定完還的設定$s抓資料順位的排位數字...
已邀請的邦友 {{ invite_list.length }}/5
Schema (MySQL v8.0)
CREATE TABLE redo_table
(
redo_date VARCHAR(10),
redo_time CHAR,
redo_admission_state CHAR,
redo_admission VARCHAR(19)
);
INSERT INTO redo_table VALUES('2020-04-01','1','0','2020-04-01 02:00:00');
INSERT INTO redo_table VALUES('2020-04-01','1','0','2020-04-01 01:00:00');
INSERT INTO redo_table VALUES('2020-04-01','1','1','2020-04-01 03:00:00');
INSERT INTO redo_table VALUES('2020-04-01','1','1','2020-04-01 02:00:00');
INSERT INTO redo_table VALUES('2020-04-01','1','1','2020-04-01 01:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','0','2020-04-01 05:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','0','2020-04-01 04:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','0','2020-04-01 03:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','0','2020-04-01 02:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','0','2020-04-01 01:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','1','2020-04-01 06:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','1','2020-04-01 05:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','1','2020-04-01 04:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','1','2020-04-01 03:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','1','2020-04-01 02:00:00');
INSERT INTO redo_table VALUES('2020-04-02','1','1','2020-04-01 01:00:00');
Query #1
SELECT
redo_date,
redo_time,
redo_admission_state,
redo_admission,
RANK() OVER (
PARTITION BY redo_date, redo_time, redo_admission_state
ORDER BY redo_admission
) therank
from redo_table;
| redo_date | redo_time | redo_admission_state | redo_admission | therank |
|---|---|---|---|---|
| 2020-04-01 | 1 | 0 | 2020-04-01 01:00:00 | 1 |
| 2020-04-01 | 1 | 0 | 2020-04-01 02:00:00 | 2 |
| 2020-04-01 | 1 | 1 | 2020-04-01 01:00:00 | 1 |
| 2020-04-01 | 1 | 1 | 2020-04-01 02:00:00 | 2 |
| 2020-04-01 | 1 | 1 | 2020-04-01 03:00:00 | 3 |
| 2020-04-02 | 1 | 0 | 2020-04-01 01:00:00 | 1 |
| 2020-04-02 | 1 | 0 | 2020-04-01 02:00:00 | 2 |
| 2020-04-02 | 1 | 0 | 2020-04-01 03:00:00 | 3 |
| 2020-04-02 | 1 | 0 | 2020-04-01 04:00:00 | 4 |
| 2020-04-02 | 1 | 0 | 2020-04-01 05:00:00 | 5 |
| 2020-04-02 | 1 | 1 | 2020-04-01 01:00:00 | 1 |
| 2020-04-02 | 1 | 1 | 2020-04-01 02:00:00 | 2 |
| 2020-04-02 | 1 | 1 | 2020-04-01 03:00:00 | 3 |
| 2020-04-02 | 1 | 1 | 2020-04-01 04:00:00 | 4 |
| 2020-04-02 | 1 | 1 | 2020-04-01 05:00:00 | 5 |
| 2020-04-02 | 1 | 1 | 2020-04-01 06:00:00 | 6 |
另外說一下
這不叫排序(ORDER BY, 依考試分數由大排到小)
這叫排名(RANK BY, 依考試分數決定的名次)
iThome鐵人賽
看影片追技術