唯一,我的意思是如果有一些值有重复项,不要显示它们中的任何一个。
例:
Student | College
-----------------------
Jake | Harvard
Josh | Penn State
Erica | Harvard
所以在这种情况下,结果将是
Penn State
查询类似于只有一个学生去的学校。
我想在不使用 count(*( 的情况下执行此操作。
不使用count
的限制听起来有点人为,但假设student
和college
的组合是唯一的,你可以比较每所大学的最大值和最小值,并确保他们是同一个学生:
SELECT college
FROM mytable
GROUP BY college
HAVING MIN(student) = MAX(student)
您可以在大学字段和不同的学生上使用左自联接,并仅返回那些不匹配的记录:
select t1.college from yourtable t1
left join yourtable t2 on t1.collage=t2.college and t1.student<>t2.student
where t2.college is null
您可以使用LAG()
和LEAD()
,如下所示:
select
*,
lag(college) over(order by college) as prev_college
lead(college) over(order by college) as next_college
from my_table
where college <> prev_college or prev_college is null
and college <> next_college or next_college is null
假设你没有严格的重复项,你可以使用not exists
来查看学院里是否有另一个学生:
select t.college
from t
where not exists (select 1
from t t2
where t2.college = t.college and t2.student <> t.student
);