求个sql语句，达人来~~~
现有一张表，供很多人打分
字段possibility里可能有值1,2,3,4,5分别代表1,2,3,4,5
字段harm里可能有值A,B,C,D分别代表1,0.5,0.2,0.1
字段controlid可能是同一值
现在要求出同controlid值的按possibility+harm组合得到的总分和平均值排序
possibility+harm可能是不同的组合
--此表我去掉了无关紧要的字段
CREATE TABLE [dbo].[test](
[controlcountid] [int] IDENTITY(1,1) NOT NULL,
[controlid] [int] NULL,
[possibility] [varchar](50) NULL,
[harm] [varchar](50) NULL )
insert into test ('1','1','A',)
insert into test ('1','2','C',)
insert into test ('2','3','D',)
insert into test ('2','4','B',)
insert into test ('3','5','A',)
insert into test ('3','1','D',)
select controlid , Sum(possibility * case harm when 'A' then 1.0 when 'B' then 0.5 when 'C' then 0.2 when 'D' then 0.1 end)
from test
group by controlid
