我有這張表，它記錄了一個人上駕駛課和在家練習的時間。我有這種情況，只要該class_practice列的值具有Simulation并且input_value列具有Yes，那么 Test_1 就會在該日期開始。隨后，Simulation欄位被替換為表中的Class值。當該類有一個Yes時， Test_2 開始，然后另一個Yes表示 Test_3 等等，Test_4、Test_5 等等....
如何在 postgreSQL 11 中實作這一點。
select * from driving_table
student_name class_practice input_value class_practice_date
Erin Practice Yes 2021-12-15
Erin Practice Yes 2021-12-16
Erin Simulation No 2021-12-16
Erin Practice Yes 2021-12-17
Erin Simulation Yes 2021-12-17
Erin Practice No 2021-12-18
Erin Class No 2021-12-18
Erin Practice No 2021-12-19
Erin Class No 2021-12-19
Erin Practice No 2021-12-20
Erin Class Yes 2021-12-20
Erin Practice No 2021-12-21
Erin Class No 2021-12-21
Erin Practice Yes 2021-12-21
Erin Class No 2021-12-21
Erin Practice No 2021-12-22
Erin Class Yes 2021-12-22
Erin Practice No 2021-12-23
Erin Class No 2021-12-23
Erin Practice No 2021-12-24
Erin Class No 2021-12-24
Danny Practice Yes 2021-12-15
Danny Practice Yes 2021-12-16
Danny Simulation No 2021-12-16
Danny Practice Yes 2021-12-17
Danny Simulation Yes 2021-12-17
Danny Practice No 2021-12-18
Danny Class Yes 2021-12-18
Danny Practice No 2021-12-19
Danny Class No 2021-12-19
Danny Practice No 2021-12-20
Danny Class Yes 2021-12-20
Danny Practice No 2021-12-21
Danny Class No 2021-12-21
Danny Practice Yes 2021-12-21
Danny Class No 2021-12-21
Danny Practice No 2021-12-22
Danny Class Yes 2021-12-22
Danny Practice No 2021-12-23
Danny Class No 2021-12-23
Danny Practice No 2021-12-24
Danny Class No 2021-12-24
要求的結果：
student_name class_practice input_value class_practice_date test_simulation
Erin Practice Yes 2021-12-15
Erin Practice Yes 2021-12-16
Erin Simulation No 2021-12-16
Erin Practice Yes 2021-12-17 Test_1
Erin Simulation Yes 2021-12-17 Test_1
Erin Practice No 2021-12-18 Test_1
Erin Class No 2021-12-18 Test_1
Erin Practice No 2021-12-19 Test_1
Erin Class No 2021-12-19 Test_1
Erin Practice No 2021-12-20 Test_2
Erin Class Yes 2021-12-20 Test_2
Erin Practice No 2021-12-21 Test_2
Erin Class No 2021-12-21 Test_2
Erin Practice Yes 2021-12-21 Test_2
Erin Class No 2021-12-21 Test_2
Erin Practice No 2021-12-22 Test_3
Erin Class Yes 2021-12-22 Test_3
Erin Practice No 2021-12-23 Test_3
Erin Class No 2021-12-23 Test_3
Erin Practice No 2021-12-24 Test_3
Erin Class No 2021-12-24 Test_3
Danny Practice Yes 2021-12-15
Danny Practice Yes 2021-12-16
Danny Simulation No 2021-12-16
Danny Practice Yes 2021-12-17 Test_1
Danny Simulation Yes 2021-12-17 Test_1
Danny Practice No 2021-12-18 Test_1
Danny Class No 2021-12-18 Test_1
Danny Practice No 2021-12-19 Test_1
Danny Class No 2021-12-19 Test_1
Danny Practice No 2021-12-20 Test_2
Danny Class Yes 2021-12-20 Test_2
Danny Practice No 2021-12-21 Test_2
Danny Class No 2021-12-21 Test_2
Danny Practice Yes 2021-12-21 Test_2
Danny Class No 2021-12-21 Test_2
Danny Practice No 2021-12-22 Test_3
Danny Class Yes 2021-12-22 Test_3
Danny Practice No 2021-12-23 Test_3
Danny Class No 2021-12-23 Test_3
Danny Practice No 2021-12-24 Test_3
Danny Class No 2021-12-24 Test_3
uj5u.com熱心網友回復：
select
coalesce('Test_' || nullif(
count(*)
filter (where class_practice in ('Simulation', 'Class') and input_value = 'Yes')
over (partition by student_name order by class_practice_date),
0)::varchar(3), '')
from driving_table
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/421784.html
標籤：
下一篇：如何根據層級進行查詢？