無論如何在不改變現有表的情況下在查詢中插入重復的行（除外語之外的所有主題兩行，這需要三行）。
select * from school_data
student_name subjects class_date
Joe Math 10/1/2021
Mike Science 10/2/2021
Eddie History 10/3/2021
Steve English 10/4/2021
John Foreign Language 10/5/2021
要求：
student_name subjects class_date
Joe Math 10/1/2021
Joe Math 10/1/2021
Mike Science 10/2/2021
Mike Science 10/2/2021
Eddie History 10/3/2021
Eddie History 10/3/2021
Steve English 10/4/2021
Steve English 10/4/2021
John Foreign Language 10/5/2021
John Foreign Language 10/5/2021
John Foreign Language 10/5/2021
uj5u.com熱心網友回復：
JOIN 生成的包含 3 行的表
select *
from school_data
join generate_series(1,3) t(n) on t.n<=2 or subjects ='Foreign Language'
order by student_name
uj5u.com熱心網友回復：
好吧，您可以創建另一個名為“subjects”的表，并根據需要多次輸入科目名稱（例如數學兩個，外語三個）。這將在加入時創建一對多的結果。只需要確保您在那里擁有所有主題。
主題表內容：
Math
Math
Science
Science
Foreign Language
Foreign Language
Foreign Language
那么一個查詢可能看起來像這樣：
select sc.student_name, sc.subject_name, sc.class_date
from student_classes sc
join subjects s
on s.subject_name = sc.subject_name
order by 1
db-fiddle 在這里找到：https : //www.db-fiddle.com/f/bYxyZidB2cPPBj3w8Pxj9H/0
編輯：如果您無法創建表，則可以使用 CTE。我確信那里有更漂亮的解決方案，但這是有效的。
with subjects as
(
select 'Math' as subject_name
union all
select 'Math' as subject_name
union all
select 'Science' as subject_name
union all
select 'Science' as subject_name
union all
select 'Foreign Language' as subject_name
union all
select 'Foreign Language' as subject_name
union all
select 'Foreign Language' as subject_name
)
select sc.student_name, sc.subject_name, sc.class_date
from student_classes sc
join subjects s
on s.subject_name = sc.subject_name
order by 1
db-fiddle 在這里找到：https : //www.db-fiddle.com/f/o4vxshi9ua3rLCNdT9oXAR/0
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/313925.html
標籤：sql PostgreSQL