两个表-一个是电子邮件和电子邮件所有者观看的视频，另一个是视频和演员表。据我所知，电子邮件所有者可以观看多个视频，演员表可以参与多个视频，并有交叉的可能性。在这两个表中（作为样本数据）：

WITH
    t_watch AS
        (
          Select 'aaa.aaa@aaa.aa' "E_MAIL", 101  "VIDEO_ID" From Dual Union All
          Select 'ccc.ccc@ccc.cc' "E_MAIL", 101  "VIDEO_ID" From Dual Union All
          Select 'aaa.aaa@aaa.aa' "E_MAIL", 102  "VIDEO_ID" From Dual Union All
          Select 'bbb.bbb@bbb.bb' "E_MAIL", 102  "VIDEO_ID" From Dual Union All
          Select 'ccc.ccc@ccc.cc' "E_MAIL", 201  "VIDEO_ID" From Dual Union All
          Select 'ddd.ddd@ddd.dd' "E_MAIL", 101  "VIDEO_ID" From Dual Union All
          Select 'ddd.ddd@ddd.dd' "E_MAIL", 201  "VIDEO_ID" From Dual Union All
          Select 'aaa.aaa@aaa.aa' "E_MAIL", 301  "VIDEO_ID" From Dual 
        ),
    t_work_in AS
        (
          Select 101 "VIDEO_ID", 'Chriss' "CAST_NAME" From Dual Union All
          Select 101 "VIDEO_ID", 'David'  "CAST_NAME" From Dual Union All
          Select 101 "VIDEO_ID", 'Annie'  "CAST_NAME" From Dual Union All
          Select 102 "VIDEO_ID", 'Chriss' "CAST_NAME" From Dual Union All
          Select 201 "VIDEO_ID", 'Chriss' "CAST_NAME" From Dual Union All
          Select 201 "VIDEO_ID", 'David'  "CAST_NAME" From Dual Union All
          Select 201 "VIDEO_ID", 'Annie'  "CAST_NAME" From Dual Union All
          Select 202 "VIDEO_ID", 'Annie'  "CAST_NAME" From Dual Union All
          Select 301 "VIDEO_ID", 'Robert' "CAST_NAME" From Dual
        ),

这个问题是关于获得一个人的电子邮件地址观看所有视频参与特定演员名称。要得到答案，你应该得到以下列表：

• 由特定电子邮件所有者观看的唯一有序视频ID
• 特定演员表名称在其中工作的唯一有序视频ID

为此，请创建两个cte- cte_watched_by 和 cte_worked_in：

cte_worked_in AS
        (
            SELECT DISTINCT
                wk.CAST_NAME, 
                LISTAGG(wk.VIDEO_ID, ', ') WITHIN GROUP (Order By wk.VIDEO_ID) OVER(Partition By wk.CAST_NAME) "WORKED_LIST",
                Count(DISTINCT wk.VIDEO_ID) OVER(Partition By wk.CAST_NAME) "COUNT_IDS_WORKED"
            FROM
                (Select DISTINCT wrk.CAST_NAME, wrk.VIDEO_ID From t_work_in wrk Left Join t_watch wtc ON(wtc.VIDEO_ID = wrk.VIDEO_ID) Order By wrk.VIDEO_ID) wk
        ), 
    cte_watched_by AS
        (
            SELECT DISTINCT
                wb.E_MAIL, 
                LISTAGG(wb.VIDEO_ID, ', ') WITHIN GROUP (Order By wb.VIDEO_ID) OVER(Partition By wb.E_MAIL) "WATCHED_LIST",
                Count(DISTINCT wb.VIDEO_ID) OVER(Partition By wb.E_MAIL) "COUNT_IDS_WATCHED"
            FROM
                (Select DISTINCT wtc.E_MAIL, wtc.VIDEO_ID From t_watch wtc Left Join t_work_in wrk ON(wrk.VIDEO_ID = wtc.VIDEO_ID) Order By wtc.VIDEO_ID) wb
        )

这是我们目前所得到的结果：

cte工作地点

| 角色名称|工作列表|已工作ID计数|
| - -|- -|- -|
| 克里斯|一百零一、一百零二、二百零一|三个|
| 罗贝尔|301个|一个|
| 安妮|一百零一、二百零一、二百零二|三个|
| 大卫w101，201| 2个||

cte观看者

| 电子邮件（_M）|监视列表|监视的ID计数|
| - -|- -|- -|
| ccc.cc：|101、201年|2个|
| 日.日@日.日|101、201年|2个|
| bbb.bb：|一百零二|一个|
| aaa.aaa @ aa.aa| 101、102、301个|三个|
现在我们可以连接这些cte并得到答案-下面是主SQL：

SELECT DISTINCT
    wk.CAST_NAME "WORKED_IN",
    wk.COUNT_IDS_WORKED "COUNT_IDS_WORKED",
    wk.WORKED_LIST,
    wb.E_MAIL "WATCHED_BY",
    wb.COUNT_IDS_WATCHED "COUNT_IDS_WATCHED",
    wb.WATCHED_LIST
FROM
    cte_worked_in wk
INNER JOIN    
    cte_watched_by wb ON
            (
                (wk.COUNT_IDS_WORKED = wb.COUNT_IDS_WATCHED And wk.WORKED_LIST = wb.WATCHED_LIST)
              OR
                (wk.COUNT_IDS_WORKED = 1 And wb.COUNT_IDS_WATCHED > 1 And InStr(wb.WATCHED_LIST, wk.WORKED_LIST) > 0)
              OR
                (wk.COUNT_IDS_WORKED = 2 And wb.COUNT_IDS_WATCHED > 2 And InStr(wb.WATCHED_LIST, SubStr(wk.WORKED_LIST, 1, 3)) > 0 And InStr(wb.WATCHED_LIST, SubStr(wk.WORKED_LIST, 6, 3)) > 0)
            )

......下面是生成的数据集：
| 工作|已工作ID计数|工作列表|观看者|监视的ID计数|监视列表|
| - -|- -|- -|- -|- -|- -|
| 罗贝尔|一个|301个|aaa.aaa @ aa.aa|三个|101、102、301个|
| 大卫|2个|101、201年|日.日@日.日|2个|101、201年|
| 大卫|2个|101、201年|ccc.cc：|2个|101、201年|
INNER JOIN的ON表达式中的OR用于涵盖这样的情况：某人看了很多电影，但演员名单只在少数几部电影中起作用，这是这个答案中最薄弱的部分。
可以过滤结果数据集（如果需要的话）以仅显示特定演员表名称和/或电子邮件的结果。
此致。

无法对此进行测试，但我认为这应该有效：

SELECT emailaddress, count(*)
FROM Watch w inner join WorkIn wi on w.videoID = wi.videoID
WHERE wi.castName = 'Chris Evans'
GROUP BY emailaddress
HAVING count(*) = (select count(*) from WorkIn where wi.castName = 'Chris Evans')

group by将计算每个电子邮件地址与Chris Evans一起观看的视频数量。
HAVING类似于聚合结果上的WHERE子句（所以在GROUP BY之后）。只有当此计数等于Chris Evans播放的视频总数时，才会返回电子邮件地址。