试试Update A
Set 单耗定额=(Select (Min(单耗定额)/Count(*)) from 表 Where [部门编码]=A.[部门编码]And [工序编码]=A.[工序编码] And [员工编码]=A.[员工编码] And [成品编码]=A.[成品编码] ) from 表 A
Set 单耗定额=(Select (Min(单耗定额)/Count(*)) from 表 Where [部门编码]=A.[部门编码]And [工序编码]=A.[工序编码] And [员工编码]=A.[员工编码] And [成品编码]=A.[成品编码] ) from 表 A
select a.部门编码,a.工序编码,a.员工编码,a.成品编码,a.易耗品编码,(a.单耗定额/b.重复次数) as 单耗定额
from
TableName a ,
(select 部门编码,工序编码,员工编码,成品编码,count(*) as 重复次数
from TableName
group by 部门编码,工序编码,员工编码,成品编码) b
where a.部门编码=b.部门编码 and a.工序编码=b.工序编码 and a.员工编码=b.员工编码 and
a.成品编码=b.成品编码
改改,就是感觉有点长
update TableName
set 单耗定额=
(select (a.单耗定额/b.重复次数) from TableName a ,
(select 部门编码,工序编码,员工编码,成品编码,count(*) as 重复次数
from TableName group by 部门编码,工序编码,员工编码,成品编码) b
where a.部门编码=b.部门编码 and a.工序编码=b.工序编码 and a.员工编码=b.员工编码 and a.成品编码=b.成品编码)
--建立测试环境
Create table TEST(
[部门编码] Varchar(10),
[工序编码] Varchar(10),
[员工编码] Varchar(10),
[易耗品编码] Varchar(50),
[成品编码] Varchar(50),
[单耗定额] Float)
GO
--插入数据
Insert TEST Values('103', '245','22071','4010402020020','2020102082004', 0.5)
Insert TEST Values('103', '245','22071','4010403', '2020102082004', 0.5)
Insert TEST Values('103', '245','22071','4010402020020','2020102082035', 0.5)
Insert TEST Values('103', '245','22071','4010403', '2020102082035', 0.5)
Insert TEST Values('103', '245','22071','4010402020020','2020102091098', 0.64)
Insert TEST Values('103', '245','22071','4010403', '2020102091098', 0.64)
Insert TEST Values('103', '245','22071','4010402020020','2020102091103', 0.64)
Insert TEST Values('103', '245','22071','4010403', '2020102091103', 0.64)
Insert TEST Values('103', '245','22071','4010402020020','2020103011063', 0.64)
Insert TEST Values('103', '245','22071','4010403', '2020103011063', 0.64)
Insert TEST Values('103', '245','22071','4010402020020','2020103011473', 0.64)
Insert TEST Values('103', '250','22071','4010403', '2020103011473', 0.64)
GO
--测试
Select * from TEST
Update A
Set 单耗定额=(Select (Min(单耗定额)/Count(*)) from TEST Where [部门编码]=A.[部门编码]And [工序编码]=A.[工序编码] And [员工编码]=A.[员工编码] And [成品编码]=A.[成品编码] ) from TEST A
Select * from TEST
--删除测试环境
Drop table TEST
--结果
/*
--Update前
[部门编码] [工序编码] [员工编码] [易耗品编码] [成品编码] [单耗定额]
103 245 22071 4010402020020 2020102082004 0.5
103 245 22071 4010403 2020102082004 0.5
103 245 22071 4010402020020 2020102082035 0.5
103 245 22071 4010403 2020102082035 0.5
103 245 22071 4010402020020 2020102091098 0.64
103 245 22071 4010403 2020102091098 0.64
103 245 22071 4010402020020 2020102091103 0.64
103 245 22071 4010403 2020102091103 0.64
103 245 22071 4010402020020 2020103011063 0.64
103 245 22071 4010403 2020103011063 0.64
103 245 22071 4010402020020 2020103011473 0.64
103 250 22071 4010403 2020103011473 0.64--Update后
[部门编码] [工序编码] [员工编码] [易耗品编码] [成品编码] [单耗定额]
103 245 22071 4010402020020 2020102082004 0.25
103 245 22071 4010403 2020102082004 0.25
103 245 22071 4010402020020 2020102082035 0.25
103 245 22071 4010403 2020102082035 0.25
103 245 22071 4010402020020 2020102091098 0.32
103 245 22071 4010403 2020102091098 0.32
103 245 22071 4010402020020 2020102091103 0.32
103 245 22071 4010403 2020102091103 0.32
103 245 22071 4010402020020 2020103011063 0.32
103 245 22071 4010403 2020103011063 0.32
103 245 22071 4010402020020 2020103011473 0.64
103 250 22071 4010403 2020103011473 0.64
*/
--建立测试环境
Create table TEST(
[部门编码] Varchar(10),
[工序编码] Varchar(10),
[员工编码] Varchar(10),
[易耗品编码] Varchar(50),
[成品编码] Varchar(50),
[单耗定额] Float)
GO
--插入数据
Insert TEST Values('103', '245','22071','4010402020020','2020102082004', 0.5)
Insert TEST Values('103', '245','22071','4010403', '2020102082004', 0.5)
Insert TEST Values('103', '245','22071','4010402020020','2020102082035', 0.5)
Insert TEST Values('103', '245','22071','4010403', '2020102082035', 0.5)
Insert TEST Values('103', '245','22071','4010402020020','2020102091098', 0.64)
Insert TEST Values('103', '245','22071','4010403', '2020102091098', 0.64)
Insert TEST Values('103', '245','22071','4010402020020','2020102091103', 0.64)
Insert TEST Values('103', '245','22071','4010403', '2020102091103', 0.64)
Insert TEST Values('103', '245','22071','4010402020020','2020103011063', 0.64)
Insert TEST Values('103', '245','22071','4010403', '2020103011063', 0.64)
Insert TEST Values('103', '245','22071','4010402020020','2020103011473', 0.64)
Insert TEST Values('103', '250','22071','4010403', '2020103011473', 0.64)
GO
--测试
Select * from TEST
Update A
Set 单耗定额=(Select (Min(单耗定额)/Count(*)) from TEST Where [部门编码]=A.[部门编码]And [工序编码]=A.[工序编码] And [员工编码]=A.[员工编码] And [成品编码]=A.[成品编码] ) from TEST A
Select * from TEST
--删除测试环境
Drop table TEST
--结果
/*
--Update前
[部门编码] [工序编码] [员工编码] [易耗品编码] [成品编码] [单耗定额]
103 245 22071 4010402020020 2020102082004 0.5
103 245 22071 4010403 2020102082004 0.5
103 245 22071 4010402020020 2020102082035 0.5
103 245 22071 4010403 2020102082035 0.5
103 245 22071 4010402020020 2020102091098 0.64
103 245 22071 4010403 2020102091098 0.64
103 245 22071 4010402020020 2020102091103 0.64
103 245 22071 4010403 2020102091103 0.64
103 245 22071 4010402020020 2020103011063 0.64
103 245 22071 4010403 2020103011063 0.64
103 245 22071 4010402020020 2020103011473 0.64
103 250 22071 4010403 2020103011473 0.64--Update后
[部门编码] [工序编码] [员工编码] [易耗品编码] [成品编码] [单耗定额]
103 245 22071 4010402020020 2020102082004 0.25
103 245 22071 4010403 2020102082004 0.25
103 245 22071 4010402020020 2020102082035 0.25
103 245 22071 4010403 2020102082035 0.25
103 245 22071 4010402020020 2020102091098 0.32
103 245 22071 4010403 2020102091098 0.32
103 245 22071 4010402020020 2020102091103 0.32
103 245 22071 4010403 2020102091103 0.32
103 245 22071 4010402020020 2020103011063 0.32
103 245 22071 4010403 2020103011063 0.32
103 245 22071 4010402020020 2020103011473 0.64
103 250 22071 4010403 2020103011473 0.64
*/
update A set [单耗定额]=
(select max([单耗定额])/count(*)
from tablename
group by [成品编码],[易耗品编码]
where A.[成品编码]=[成品编码] and A.[易耗品编码]=[易耗品编码]
) from tablename Aselect * from tablename