所以我创建了3个值为的表
#创建参与方表
DROP TABLE IF EXISTS parties,districts,candidates;
CREATE TABLE parties ( party char(12) NOT NULL, PRIMARY KEY (party) );
INSERT INTO parties VALUES ('Conservative'),('Liberal'), ('Socialist'),('Green'),('Libertarian');
#创建地区表
CREATE TABLE districts ( district char(10) DEFAULT NULL );
INSERT INTO districts VALUES ('Essex'), ('Malton'),('Riverdale'),('Guelph'),('Halton');
#创建候选人表
CREATE TABLE candidates ( id int(11) NOT NULL, name char(10) DEFAULT NULL, district char(10) DEFAULT NULL, party char(10) DEFAULT NULL, PRIMARY KEY (id) );
INSERT INTO candidates VALUES (1,'Anne Jones','Essex','Liberal'), (2,'Mary Smith','Malton','Liberal'), (3,'Sara Black','Riverdale','Liberal'), (4,'Paul Jones','Essex','Socialist'),
(5,'Ed White','Essex','Conservative'), (6,'Jim Kelly','Malton','Liberal'), (7,'Fred Price','Riverdale','Socialist'), (8,'Bill Green','Guelph','Green'),
(9,'Garth Adams','Halton','Libertarian'), (10,'Sam Adams','Guelph','Liberal'), (11,'Jill Mackay','Halton','Liberal');
现在我想知道哪些政党在所有地区都有候选人
我已经这样做了,但我不确定这是否是正确的方式!!有人能给我指路吗?
select p.party
from parties p
inner join candidates c on p.party = c.party
inner join districts d on c.district = d.district;
您可以使用聚合。然后使用having
计算地区数量,并查看是否全部包括在内:
select p.party
from parties p inner join
candidates c
on p.party = c.party
group by p.party
having count(distinct district) = (select count(*) from districts);
count(distinct)
处理一个政党在一个地区有多个候选人的情况。
双重反加入(排除存在的地区的政党,而没有该政党的候选人(:
SELECT * FROM parties p
WHERE NOT EXISTS (
SELECT * FROM districts dx
WHERE NOT EXISTS (
SELECT * FROM candidates cx
WHERE cx.district = dx.district
AND cx.party = p.party
)
);