sql - 最大投票百分比

Question

假设一个小镇正在举行选举，每个公民都可以投票多次，但他们投票的次数越多，他们的选票就会分裂。

我的输入如下

表：town_elections

Voter Vote
Jane Jane 
Howard Jane
John Howard
John Jane
Jane John
Sara Howard

期待：

Jane    Jane   0.33
Howard  Jane   0.33
John    Howard 0.5
John    Jane   0.33
Jane    John   1.0
Sara    Howard 0.5

标签： sqlsql-servertsqlsql-server-2012

Answer 1

试试下面的查询。cast to decimal在这里使用您想要的结果

with MyCTE
as
(
select 
voter,
Vote,
count(*) over(PARTITION BY Vote order 
by Vote) as votecount
from town_elections
)
select Voter,Vote, cast((cast(100 as decimal(18,2))/cast(votecount as 
decimal(18,2)))/100.00 as decimal(18,2)) as percentage from MyCTE