查询问题

[BearRui]

有个表，结构如下

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

[BearRui]

楼上的min取的是最小的employeeid,请问要怎么取第一个employeeid，就是按SQL中存储的顺序

[mengmou]

表中有哪些索引？

[BearRui]

没有索引，就是上面的表结构，因为是我产生的临时表

[Generics]

create table #tc
(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.PK

Alter table #tc drop column PK

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

[mengmou]

“SQL中存储的顺序”不明确，最好加个表示列

[tomyuansir]

楼上的正解

[BearRui]

把楼上的copy到查询分析器中怎么执行不了，报错：
服务器: 消息 207，级别 16，状态 3，行 18
列名 'PK' 无效。

[晓风残月0110]

不能啊
楼上的正解

[lass_name]

楼上的是最棒的！！

[BearRui]

把2个人的方法结合了下。谢谢各位

[$扫地僧$]

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

[$扫地僧$]

--更新1
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