有个表,结构如下roomno employeeid type money
101 123 1 23
101 1235 1 23
101 1232 1 23
102 1234 3 20
102 125 3 20
104 111 1 40
103 126 3 15
103 130 3 15
-------------------
上面的数据employeeid是唯一的,现在我想更新这个数据,把type为3并且roomno相同的人的money加起来算在1个人头上,而另1个人的money为0,就是102房有2个人,我想把这2个人的钱算在1234这个人的头上。像得到这样的结果。
101 123 1 23
101 1235 1 23
101 1232 1 23
102 1234 3 40
102 125 3 0
104 111 1 40
103 126 3 30
103 130 3 0
101 123 1 23
101 1235 1 23
101 1232 1 23
102 1234 3 20
102 125 3 20
104 111 1 40
103 126 3 15
103 130 3 15
-------------------
上面的数据employeeid是唯一的,现在我想更新这个数据,把type为3并且roomno相同的人的money加起来算在1个人头上,而另1个人的money为0,就是102房有2个人,我想把这2个人的钱算在1234这个人的头上。像得到这样的结果。
101 123 1 23
101 1235 1 23
101 1232 1 23
102 1234 3 40
102 125 3 0
104 111 1 40
103 126 3 30
103 130 3 0
from 表 A,
(
select roomno,min(employeeid) as employeeid,sum([money]) as [money]
from 表
where type=3
group by roomno
) T
where A.roomno=T.roomno
and A.employeeid=T.employeeid
update 表 set [money]=T.[money]
from 表 A,
(
select roomno,min(employeeid) as employeeid,sum([money]) as [money]
from 表
where type=3
group by roomno
) T
where A.roomno=T.roomno
and A.employeeid=T.employeeid--更新2
update 表 set [money]=0
from 表 A
where exists(
select 1
from
(
select roomno,min(employeeid) as employeeid
from 表
where type=3
group by roomno
) T
where A.roomno=roomno and A.roomno<>employeeid
)
and A.type=3
(roomno int, employeeid int, [type] int, [money] int)insert #tc
select 101, 123, 1, 23
union all select 101, 1235, 1, 23
union all select 101, 1232, 1, 23
union all select 102, 1234, 3, 20
union all select 102, 125, 3, 20
union all select 104, 111, 1, 40
union all select 103, 126, 3, 15
union all select 103, 130, 3, 15 Alter table #tc add PK int identity(1,1)update #tc
set [money] = T.[money]
from #tc
JOIN (select roomno, sum([money]) as [money], min(PK) as PK from #tc where [type] = 3 group by roomno
UNION select roomno, 0 as [money], PK from #tc T1 where [type] = 3
AND PK <> (select min(PK) from #tc where [type] = 3 AND roomno = T1.roomno group by roomno)) T
ON #tc.PK = T.PKAlter table #tc drop column PKselect * from #tc
drop table #tc结果:
101 123 1 23
101 1235 1 23
101 1232 1 23
102 1234 3 40
102 125 3 0
104 111 1 40
103 126 3 30
103 130 3 0
服务器: 消息 207,级别 16,状态 3,行 18
列名 'PK' 无效。