关于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)

Answer 2

以下是如何获得基于政党的注册选民的百分比。使用sum()根据case语句条件获取值。然后除以总count()

select sum(case when b.Registered = 1 and p.Party in ('Dem', 'Rep') then 1 else 0 end)/count(1) * 100 as ThirdParty
    , sum(case when b.Registered = 1 and p.Party not in ('Dem', 'Rep') then 1 else 0 end)/count(1) * 100 as NonThirdParty
from Vote v
inner join Voter b on b.Person_ID = v.Person_ID
inner join Person p on p.Person_ID = v.Person_ID