Sql存储过程---计算出投票百分比

1。建表：Vote主表和VoteOption选项表

Vote表：

Vote测试数据：

VoteOption表：

 

Option测试数据：

现在要计算出投票名称为“第二个投票”的各项投票数的百分比

存储过程如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[cms_GetVoteOptions]
 @VoteGuid varchar(36)
AS

DECLARE @total_count int
SELECT @total_count = ISNULL(( SELECT SUM(VoteNum) FROM [cms_VoteOption] WHERE VoteGuid = @VoteGuid),0)

IF @total_count <> 0
SELECT OptionID,VoteGuid,VoteContent,VoteNum, (100 * VoteNum + @total_count / 2) / @total_count AS per FROM [cms_VoteOption]
WHERE VoteGuid = @VoteGuid

ELSE
SELECT OptionID,VoteGuid,VoteContent,VoteNum, 0 AS per FROM [cms_VoteOption]
WHERE VoteGuid = @VoteGuid

 

测试语句：exec [cms_GetVoteOptions] 'd080ccd4-8b05-4b2e-83af-f0bfac423803'

执行结果：