有关Select语句的SQL问题

2024-05-06 • 问答

第三方注册选民实际投票的百分比是多少？非第三方注册选民的实际投票率是多少？

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)
);

从不在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)

以下是根据政党获得注册选民百分比的方法。使用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

有关Select语句的SQL问题

chenglong502 回答：有关Select语句的SQL问题

大家都在问