查找授课的校园

Question

我有CAMPUS_IDs(NY1、PA1、VA1)和CLASS_IDs(大约10个班级)，所以我必须创建一个包含列Campus_id和Class_id的表，并指定这些班级在哪个校园授课。问题是，有些课程在所有校园内授课，而有些课程只在一个校园内授课。如何设计那张表？我试着创建下面的表，但是它很乱：

_____________________
| class_id  |campus_id
---------------------
| math101   | NY1    |
---------------------
| pyt218    | PA1    |
---------------------
| C767      | VA1    | 
---------------------
| pyt218    | PA1    |
_____________________

带有class_id的campus_id是另一个表的外键。

Answer 1

所以你想列出所有授课的校区？对于如下的输出

class_id | campus_id
math101  | NY1, PA1
pyt218   | PA1, VA1, NY1
...

您可以使用Oracle10g中提供的wm_concat函数：

with d as (
  select 'NY1' as campus_id, 'math101' as class_id from dual union all 
  select 'PA1' as campus_id, 'math101' as class_id from dual union all 
  select 'VA1' as campus_id, 'pyt218' as class_id from dual union all 
  select 'PA1' as campus_id, 'pyt218' as class_id from dual union all 
  select 'PA1' as campus_id, 'class3' as class_id from dual union all 
  select 'VA1' as campus_id, 'class3' as class_id from dual union all 
  select 'VA1' as campus_id, 'math101' as class_id from dual union all 
  select 'NY1' as campus_id, 'class3' as class_id from dual
)
select class_id, wm_concat(campus_Id)
From d
group by class_id

在Oracle11.2中，有一个新函数"listagg“，可用于生成相同的结果：

with d as (
  select 'NY1' as campus_id, 'math101' as class_id from dual union all 
  ...
)
select class_id, listagg(campus_id, ',') within group (order by campus_id)
From d
group by class_id