SQL查找唯一值 [英] SQL find unique values

问题描述

我试图找到一个选择唯一值的语句.不像不同/独特，因为这些只是删除重复项.我想获得所有唯一值的列表，只有一个条目.

Im trying to find a statement for selecting unique values. Not like distinct/unique, cause these just remove duplicates. I want to get a list of all the values that are unique, only one entry.

例如:

值:1、2、3、4、4、5、5、6.

Values: 1, 2, 3, 4, 4, 5, 5, 6.

我想得到:1、2、3、6.

I would like to get: 1, 2, 3, 6.

问题是:哪些演员(电影名称和数量)在超过 199 部电影中扮演了具有独特角色名称的角色?

The problem is: What actors (name and number of movies) have played roles with a unique character name in more than 199 movies?

这些是我的表格:

Table "public.filmparticipation"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 partid   | integer | 
 personid | integer | not null
 filmid   | integer | not null
 parttype | text    | not null


Table "public.filmcharacter"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 partid        | integer | 
 filmcharacter | text    | 
 billingpos    | integer |


Table "public.person"
  Column   |     Type     | Modifiers 
-----------+--------------+-----------
 personid  | integer      | 
 lastname  | text         | not null
 firstname | text         | 
 gender    | character(1) |

这是我迄今为止尝试过的，虽然我什至还没有接近我认为的解决方案:

This is what I have tried so far, although I'm not even close to a solution I think:

SELECT p.firstname, COUNT(fp.filmid)
FROM person p INNER JOIN filmparticipation fp
ON p.personid = fp.personid
INNER JOIN filmcharacter fc
ON fc.partid = fp.partid
GROUP BY p.firstname
HAVING COUNT(fc.filmcharacter) = 1;

谢谢.

推荐答案

一个简单的方法使用group by和have:

One simple method uses group by and having:

select val
from t
group by val
having count(*) = 1;

这篇关于SQL查找唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！