建议用存储过程
create procedure usp_myprocedure as
begin
select * into #tmp_admin from YourTable where Manage='admin'
select * into #tmp_suo from YourTable where Manage='suo'
select * into #tmp_yuan from YourTable where Manage='yuan' select A.UserID, A.TestT,(A.s * 0.3 + B.s * 0.3 + C.s * 0.4) as MarksTotal from #tmp_admin A,#tmp_suo B,#tmp_suo C
where A.UserID=B.UserID and A.userID=C.UserID
end//未考虑数据不平衡情况
create procedure usp_myprocedure as
begin
select * into #tmp_admin from YourTable where Manage='admin'
select * into #tmp_suo from YourTable where Manage='suo'
select * into #tmp_yuan from YourTable where Manage='yuan' select A.UserID, A.TestT,(A.s * 0.3 + B.s * 0.3 + C.s * 0.4) as MarksTotal from #tmp_admin A,#tmp_suo B,#tmp_suo C
where A.UserID=B.UserID and A.userID=C.UserID
end//未考虑数据不平衡情况
你的执行了,不行啊,得到如下这样的结果,不是我要的:
A0001 能力 9.3
A0001 能力 9.6
A0001 能力 9.7
A0001 能力 10
A0001 态度 9
A0001 态度 9.3
A0001 态度 9.4
A0001 态度 9.7
B0001 能力 9.3
B0001 能力 9
B0001 能力 8.9
B0001 能力 8.6
B0001 态度 8.4
B0001 态度 8.1
B0001 态度 8
B0001 态度 7.7
UserID VARCHAR(20),
TestT VARCHAR(10),
Manage VARCHAR(10),
Marks INT
)*/
SELECT USERId,TestT,sum (case when manage ='admin' then 0.3*Marks when manage='suo' then 0.3*Marks else 0.4*Marks end ) as MarksFROM tblCeShi group by userid,testT
select * from tblCeShi希望可以帮你!!!
是够虚拟的!!!