插入主题的重复行

Question

是否在不改变现有表的情况下在查询中插入重复行(除了需要三行的外文之外，所有主题都有两行)。

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

Answer 1

使用包含3行的生成表连接

select * 
from school_data
join generate_series(1,3) t(n) on t.n<=2 or subjects ='Foreign Language'
order by student_name

Answer 2

那么，您可以创建另一个名为“subject”的表，并尽可能多地输入主题名称(比如两个用于数学，三个用于外语)。这将在加入它时产生一对多的结果。只需要确保你把所有的科目都放在里面。

主题表目录：

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-小提琴在这里找到：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-小提琴在这里找到：https://www.db-fiddle.com/f/o4vxshi9ua3rLCNdT9oXAR/0