mysql - 有关 Select 语句的 SQL 问题

Question

第三方登记选民实际投票的百分比是多少？非第三党登记选民实际投票的百分比是多少？

Select count(*)
From Vote V, Voter B, Person P
Where V.Person_ID = B.Person_ID AND B.Register ‘1’ AND (P.Party IN ( ‘Dem’ , ‘Rep’ );

这包括第三国家党内有多少人登记投票，但我不明白如何将其除以总票数。

CREATE TABLE Vote(
  Vote_ID int,
  Person_ID int,            //V.Person_ID, C.Person_ID
  Title varchar(50),
  PRIMARY KEY(Vote_ID)
);

CREATE TABLE Person(
  Person_ID int,
  First_Name varchar (50),
  Mid_Name varchar(50),
  Last_Name varchar(50),
  Street varchar(45),
  Zipcode int,
  Party varchar(20),
  PRIMARY KEY (Person_ID)
);

CREATE TABLE Voter(
  Person_ID int,
  Registered int,
  PRIMARY KEY(Person_ID)
);

Answer 1

切勿在FROM子句中使用逗号。 始终使用正确、明确、标准的JOIN语法。

您的数据需要更多解释，因为人们可以多次投票。据推测，您想要的人曾经投票过。

问题的结构建议这样的查询:

select (case when party in ('Dem', 'Rep') 
               then 'DemRep' else '3rdParty'
         end),
       count(v.person_id) * 1.0 / count(*)
from person p left join
     (select distinct v.person_id
      from vote v
     ) v 
     on v.person_id = p.person_id
where exists (select 1
              from voter vr
              where vr.register = 1 and vr.personid = v.person_id
             )
group by (case when party in ('Dem', 'Rep') 
               then 'DemRep' else '3rdParty'
          end)