代码之家 › 专栏 › 技术社区 › lumos

基于单个计数值从联接表中排除结果

sql-server sql

lumos · 技术社区 · 7 年前

在这个例子中,我有一个名为 movie_info ,其中包含有关电影的信息,每个都具有唯一的ID。第二个表名为 crew_info 有关于每部电影的摄制组的信息(带有电影的唯一ID),但不是每部电影有一个摄制组,而是每个摄制组有多个摄制组。从视觉上看应该是这样的:

+----------------------+
|       movie_info     |
+======================+
|       id  = '123'    |  
+----------------------+

+----------------------+
|       crew_info      |
+======================+
|       id = '123'     |
+----------------------+
|     name = 'John'    |
+----------------------+
|   role = 'director'  |
+----------------------+

+----------------------+
|       crew_info      |
+======================+
|       id = '123'     |
+----------------------+
|     name = 'Mary'    |
+----------------------+
|   role = 'director'  |
+----------------------+

+----------------------+
|       crew_info      |
+======================+
|       id = '123'     |
+----------------------+
|      name = 'Sue'    |
+----------------------+
|     role = 'writer'  |
+----------------------+

我像这样把两张桌子连在一起:

SELECT a.id, b.*
FROM movie_info as a
LEFT JOIN crew_info as b
on a.id = b.id

到目前为止都是标准的。但我想做的是只有返回结果,其中 船员信息 只有一个导演。所以像这样的独立查询:

SELECT id
FROM crew_info
WHERE role = 'director'
HAVING count(id) = 1

成功排除了类似于此示例的结果,其中有多个控制器。但我该如何加入 电影信息 表,所以它都在一个查询中?

如果不清楚的话,我很抱歉。我对SQL还比较陌生,所以如果有什么地方我没有正确表达,请告诉我。谢谢您。

编辑: 是大于1时,如果另一个值匹配,如何仍然包含结果?所以我们说还有一个字段叫做 sequel_id ,只有当电影是续集时才填写。我想排除具有控制器计数>1和空或空的结果 ,但是包括具有控制器计数>1和有效 续集 (HAVING COUNT(*) = 1 OR (HAVING COUNT(*) > 1 AND sequel_id IS NOT NULL)) 但我有语法错误。

2 回复 | 直到 7 年前

Salman Arshad 7 年前

只需将现有查询用作现有子查询:

SELECT *
FROM movie_info
WHERE EXISTS (
    SELECT 1
    FROM crew_info
    WHERE crew_info.id = movie_info.id
    AND crew_info.role = 'director'
    HAVING COUNT(*) = 1
)
OR sequel_id IS NOT NULL

Salman Arshad 7 年前

使用cte进行如下尝试

with cte as
(
SELECT id
FROM crew_info
WHERE role = 'director'
HAVING count(id) > 1
) select a.*,b.id  FROM movie_info as a
LEFT JOIN cte as b
on a.id = b.id

select a.*,b.id FROM movie_info as a
left join (
          SELECT id
         FROM crew_info
         WHERE role = 'director'
         group by id
         HAVING count(id) > 1
          ) b on a.id=b.id