Mariadb中的相交查询

Question

我在MariadB语言中对此查询有问题。我想与同一字段进行相交，但具有两个值。问题是我无法使用 Intersect 查询。

我该怎么做？我尝试过存在并内部加入，但它仍然不起作用。

SELECT nombre 
FROM actores 
WHERE codactor IN ( SELECT actor 
                    FROM participacion 
                    WHERE (titulo,año) IN (SELECT titulo, año 
                                           FROM peliculas 
                                           WHERE director IN (
                                                SELECT coddirector 
                                                FROM directores d 
                                                WHERE d.nombre='Alejandro' 
                                                 AND d.apellido='Amenabar')))
INTERSECT
SELECT nombre 
 FROM actores 
 WHERE codactor IN ( SELECT actor 
                     FROM participacion 
                     WHERE (titulo,año) IN (SELECT titulo,año 
                                            FROM peliculas  
                                            WHERE director in ( 
                                                  SELECT coddirector 
                                                  from directores p 
                                                  WHERE  p.nombre='Pedro' 
                                                   AND p.apellido='Almodobar')));

Answer 1

INTERSECT在mariadb 10.3.0中引入。

Intersect

相交的结果是左右选择结果的交点，即两个结果集中存在的记录都将包含在操作结果中。

(SELECT e_name AS name, email FROM employees)
INTERSECT
(SELECT c_name AS name, email FROM customers);

至于您的查询，您可以保持原样。

Answer 2

首先尝试一下，检查您拥有所有演员。我必须添加codactor，以防您有同名演员。

SELECT a.codactor, a.nombre -- add ', *'  to see all columns and test query is ok. 
FROM actores a
JOIN participacion p
  ON a.codactor = p.actor
JOIN peliculas m 
  ON p.titulo = m.titulo
 AND p.ano = m.ano
JOIN directores d
  ON p.director = d.coddirector
WHERE (d.nombre = 'Alejandro' and d.apellido = 'Amenabar')
   OR (d.nombre = 'Pedro'     and d.apellido = 'Almodobar')

然后添加GROUP BY以查看两位导演的电影中的哪个演员。

GROUP BY a.codactor, a.nombre
HAVING COUNT(DISTINCT coddirector) = 2