Question

我用两张桌子来询问一些数据。问题是,问题永远不会停止,永远不会产生任何结果。

任务是让所有男性行为者占多数。

filmparticipation(partid, personid, filmid, parttype)

person(personid, lastname, firstname, gender)

她是我的尝试,有人能否让我对完成任务感到 h?

SELECT (COUNT(p.personid) / COUNT(a.person)) * 100
FROM person p, person a, filmparticipation f
WHERE
f.parttype =  cast  AND
p.gender =  M ;

Answer 1

You had no ON clauses for your joins, so you were joining every record with every other record across three tables! Instead, try something like this:

select (count(case when p.gender =  M  then 1 end) / count(*)) * 100
from person p
inner join filmparticipation f on p.personid = f.personid
where f.parttype =  cast

Answer 2

如何:

SELECT (COUNT(p.personid) / subq.total) * 100
FROM person p, (select count(personID) as total from person) subq, filmparticipation f
WHERE
f.parttype =  cast  
and f.personid = p.personid    
and p.gender =  M ;

页: 1 此前,你曾两次从<条码>人中挑选过这个问题,并且没有参加第二次甄选(<条码>a),但可能会导致一名漫画家加入(最终会回来,但可能不会同时返回)。

Answer 3

问题的一部分是,你没有加入表格——你再次不问他们之间的关系,因此试图将所有可能的记录合并到所有表格中。你们想要的是:

SELECT COUNT(p.personid) AS ActorCount, 
       SUM(CASE WHEN p.gender =  M  THEN 1 ELSE 0 END) AS MaleActorCount, 
       (SUM(CASE WHEN p.gender =  M  THEN 1 ELSE 0 END) / COUNT(p.personid) * 100) AS PercentMaleActors
FROM person p
INNER JOIN filmparticipation f ON p.personid = f.personid
WHERE f.parttype =  cast

友情链接