有表fen
id 分 评分人 职务
1 80 王 正职
1 100 章 副职
1 75 刘 副职
1 60 周 正职
1 90 程 副职
2 85 王 正职1的得分=正职平均分*0.6+副职平均分*0.4
得分表为defen
id defen
1 *****
2 *****求一条update defen set defen=…… sql语句
id 分 评分人 职务
1 80 王 正职
1 100 章 副职
1 75 刘 副职
1 60 周 正职
1 90 程 副职
2 85 王 正职1的得分=正职平均分*0.6+副职平均分*0.4
得分表为defen
id defen
1 *****
2 *****求一条update defen set defen=…… sql语句
from (select id,职务,avg( 分) as f from fen group by id,职务)a
group by id
set defen = a.ff
from defen f left join
(
select id,sum(case when 职务 = '正职' then 0.6 else 0.4 end * f) as ff
from (select id,职务,avg( 分) as f
from fen
group by id,职务)a
group by id
) a on f.id = a.id
insert @t select 1, 80, '王' , ' 正职'
insert @t select 1, 100, '章' , ' 副职'
insert @t select 1, 75, '刘' , '副职'
insert @t select 1, 60, '周' , '正职'
insert @t select 1, 90, ' 程' , '副职'
insert @t select 2, 85, ' 王' , '正职'
select id,sum(case when 职务 = '正职' then 0.6 else 0.4 end * 分數)
from (select id,职务,avg( 分) as 分數 from @t group by id,职务)a
group by id
id
----------- ----------------------------------------
1 140.8
2 51.0(2 row(s) affected)
SET defen =b.defen
FROM defen a INNER JOIN
(
SELECT ID,
defen=CASE WHEN 职务=N'正职' THEN totalfen*0.6 ELSE totalfen*0.4 END
FROM (
SELECT ID,职务,avg(fen) as totalfen
FROM fen
GROUP BY ID ,职务
)tmp
GROUP BY ID
) b
WHERE a.ID=b.ID
insert into tb values(1 , 80 , '王' , '正职')
insert into tb values(1 , 100 , '章' , '副职')
insert into tb values(1 , 75 , '刘' , '副职')
insert into tb values(1 , 60 , '周' , '正职')
insert into tb values(1 , 90 , '程' , '副职')
insert into tb values(2 , 85 , '王' , '正职')
goselect id , defen = sum(case 职务 when '正职' then avg_score * 0.6 else avg_score * 0.4 end) from
(
select id , 职务 , avg(分*1.0) avg_score from tb group by id , 职务
) t
group by iddrop table tb/*
id defen
----------- ----------------------------------------
1 77.333333
2 51.000000(所影响的行数为 2 行)
*/
from @defen a left join
(
select id,sum(case when 职务 = '正职' then 0.6 else 0.4 end * f) as df
from (select id,职务,avg(分*1.0) as f from @fen group by id,职务) t
group by id
) b on a.ID=b.ID
GO
INSERT INTO FEN
SELECT 1,80,'王','正职' UNION ALL
SELECT 1,100,'章','副职' UNION ALL
SELECT 1,75,'刘','副职' UNION ALL
SELECT 1,60,'周','正职' UNION ALL
SELECT 1,90,'程','副职' UNION ALL
SELECT 2,85,'王','正职'
GO
SELECT * FROM FEN
GO
CREATE TABLE DEFEN(ID INT,DEFEN INT)
GO
INSERT INTO DEFEN
SELECT 1,0
GO
SELECT * FROM DEFEN
GO
UPDATE DEFEN SET DEFEN=A.DEFEN FROM (SELECT ID,DEFEN=SUM(CASE WHEN DUTY='正职' THEN FEN ELSE 0 END)*0.6+SUM(CASE WHEN DUTY='副职' THEN FEN ELSE 0 END)*0.4
FROM FEN GROUP BY ID)A WHERE DEFEN.ID=A.ID楼主结贴吧.
CREATE TABLE FEN(ID INT,FEN INT,MAN NVARCHAR(1),DUTY NVARCHAR(2))
GO
INSERT INTO FEN
SELECT 1,80,'王','正职' UNION ALL
SELECT 1,100,'章','副职' UNION ALL
SELECT 1,75,'刘','副职' UNION ALL
SELECT 1,60,'周','正职' UNION ALL
SELECT 1,90,'程','副职' UNION ALL
SELECT 2,85,'王','正职'
GO
SELECT * FROM FEN
GO
CREATE TABLE DEFEN(ID INT,DEFEN numeric(5,2))--改了数据类型
GO
INSERT INTO DEFEN SELECT 1,0
INSERT INTO DEFEN SELECT 2,0
-----------------------------------
update DEFEN set DEFEN=b.DE from DEFEN a left join
( select id,round(isnull(avg(case when duty='正职' then fen*1.0 end),0)*0.6+
isnull(avg(case when duty='副职' then fen*1.0 end),0)*0.4,2)DE
from FEN group by id
)b on a.id=b.id
--------------------------
SELECT * FROM DEFEN
--------------------
1 77.33
2 51.00
( select id,avg(case when duty='正职' then fen*1.0 end) av1,
avg(case when duty='副职' then fen*1.0 end) av2
from FEN group by id
)b on a.id=b.id
--------------------------
SELECT * FROM DEFEN
1 77.33
2 85.00