Question

SQL问题 求2个字段的百分比

select TOP (100) PERCENT dbo.vitem.Iid, COUNT(dbo.vote_c.Iid) AS num

cast(100*(case COUNT(dbo.vote_c.Iid) when 'a' then 1 else 0 end)/COUNT(dbo.vitem.Vid) as p)+'%' as percentage

FROM dbo.vitem INNER JOIN
dbo.vote_c ON dbo.vitem.Iid = dbo.vote_c.Iid
WHERE (dbo.vitem.flag = 0) AND (dbo.vote_c.flag = 0)
GROUP BY dbo.vitem.Iid, dbo.vitem.Itemtext, dbo.vitem.Vid

--Iid 是投票人的Id ，vid是投票的编号

我想要的是投票人/投票总数 显示百分比 报错是 cast附近有语法错误 希望各位帮忙看看，谢谢

Answer 1

select TOP (100) PERCENT dbo.vitem.Iid, COUNT(dbo.vote_c.Iid) AS num

cast(100*(case COUNT(dbo.vote_c.Iid) when 'a' then 1 else 0 end)/COUNT(dbo.vitem.Vid) as varchar(10))+'%' as percentage

FROM dbo.vitem INNER JOIN
dbo.vote_c ON dbo.vitem.Iid = dbo.vote_c.Iid
WHERE (dbo.vitem.flag = 0) AND (dbo.vote_c.flag = 0)
GROUP BY dbo.vitem.Iid, dbo.vitem.Itemtext, dbo.vitem.Vid

Answer 2

case COUNT(dbo.vote_c.Iid) when 'a' then 1 else 0 end

'a'和1类型不同，当然不行啊

COUNT(dbo.vote_c.Iid)怎么会去和字符串比

Answer 3

cast(表达式 as 数据类型)