select case when 评价1 = '非常好' then 5 when 评价1 = '好' then 3 when 评价1 = '一般' then 1 end + case when 评价2 = '非常好' then 5 when 评价2 = '好' then 3 when 评价2 = '一般' then 1 end + case when 评价3 = '非常好' then 5 when 评价3 = '好' then 3 when 评价3 = '一般' then 1 end 总和 , * from tb order by 总和 desc
包装一下 select (sum(case when 评价1 = '非常好' then 5 when 评价1 = '好' then 3 when 评价1 = '一般' then 1 end) + sum(case when 评价2 = '非常好' then 5 when 评价2 = '好' then 3 when 评价2 = '一般' then 1 end) + sum(case when 评价3 = '非常好' then 5 when 评价3 = '好' then 3 when 评价3 = '一般' then 1 end)) as 总和 , * from tb order by 总和 desc 这样?建议LZ把表结构贴出来发帖注意事项http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
try select id, sum(voteNum*(case when voteItem='非常好' then 5 when voteItem='好' then 3 when voteItem='一般' then 1 end) ) as 总分 from tb group by id
select id, sum(voteNum*(case when voteItem='非常好' then 5 when voteItem='好' then 3 when voteItem='一般' then 1 end) ) as 总分 from tb group by id order by 2 desc --由高到低排序
when 评价1 = '好' then 3
when 评价1 = '一般' then 1
end +
case when 评价2 = '非常好' then 5
when 评价2 = '好' then 3
when 评价2 = '一般' then 1
end +
case when 评价3 = '非常好' then 5
when 评价3 = '好' then 3
when 评价3 = '一般' then 1
end 总和 , *
from tb order by 总和 desc
select (sum(case when 评价1 = '非常好' then 5
when 评价1 = '好' then 3
when 评价1 = '一般' then 1
end) +
sum(case when 评价2 = '非常好' then 5
when 评价2 = '好' then 3
when 评价2 = '一般' then 1
end) +
sum(case when 评价3 = '非常好' then 5
when 评价3 = '好' then 3
when 评价3 = '一般' then 1
end)) as 总和 , *
from tb order by 总和 desc
这样?建议LZ把表结构贴出来发帖注意事项http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
select
id,
sum(voteNum*(case
when voteItem='非常好' then 5
when voteItem='好' then 3
when voteItem='一般' then 1
end)
) as 总分
from
tb
group by id
id,
sum(voteNum*(case
when voteItem='非常好' then 5
when voteItem='好' then 3
when voteItem='一般' then 1
end)
) as 总分
from
tb
group by id
order by 2 desc --由高到低排序