sql查询的附加条件

Question

选择学生参加所有比赛的团体是必要的。我提出了这样的要求。但是，当这个群体中只有一个这样的学生时，这个条件就属于它的范围。你需要两个或更多。

CREATE VIEW V3 AS
SELECT Groups
FROM R3
JOIN R1 USING (name)
GROUP BY Groups
HAVING COUNT (DISTINCT Competition) = (SELECT COUNT (DISTINCT Competition) FROM R1);

表格

CREATE TABLE R1(
name VARCHAR(100),
article VARCHAR(100),
Competition VARCHAR(100));

CREATE TABLE R3 (
name VARCHAR(100),
Groups VARCHAR(100)
);

我尝试添加条件计数(不同的组)>1，但它不起作用。

Answer 1

这是一种方法：

with cte as (select r1.name, Groups_c, count(r1.name) over(partition by Groups_c) cnt2
from r1
left join r3 on r1.name = r3.name
group by  r3.name,  Groups_c
having count(distinct Competition) = (select count(distinct Competition) from r1))
select r3.Groups_c
from r3
left join cte on r3.Groups_c = cte.Groups_c
group by r3.Groups_c
having count (distinct r3.name) = cte.cnt2 

演示