求一条sql语句，关于计算求和的

[yudf]

有表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语句

[ilovewalk]

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 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

楼主结贴吧.

[wgzaaa]

[Quote=引用 7 楼 ilovewalk 的回复:]
楼主结贴吧. [/Quote]你这个不能结，不过上面有答案了，还可以简化一下：
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

[yudf]

真是谢谢大家了，还有个问题的，
id为2的只有正职评分没有副职评分，2的得分应该是85

[wgzaaa]

update DEFEN set DEFEN=isnull(av1,av2)*0.6+isnull(av2,av1)*0.4 from DEFEN a left join
( 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

[zhangyisc]

[Quote=引用 1 楼 happyflystone 的回复:]
SQL codeselectid,sum(casewhen职务='正职'then0.6else0.4end*f)from(selectid,职务,avg( 分)asffromfengroupbyid,职务)agroupbyid
[/Quote]
正解，理解起来有点费劲。

[-狙击手-]

select id,sum(case when 职务 = '正职' then 0.6 else 0.4 end * f)
from (select id,职务,avg( 分) as f from fen group by id,职务)a
group by id

[-狙击手-]

update f
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

[水族杰纶]

declare @t table(id int , 分 int, 评分人 varchar(6), 职务 varchar(6))
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)

[Garnett_KG]

UPDATE  defen
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

[dawugui]

create table tb(id int, 分 int, 评分人 varchar(10) , 职务 varchar(10))
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  , '王' , '正职') 
go

select 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 id

drop table tb

/*
id          defen                                    
----------- ---------------------------------------- 
1           77.333333
2           51.000000

（所影响的行数为 2 行）
*/

[wxg22526451]

update  @defen set  defen=b.df
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

[ilovewalk]

呵呵,眼花,没仔细看,还要AVG().