如何将表中的相同id号的第一条记录取出来?即:
id name
11 aaaa
22 dddd
CREATE TABLE #a (
[id] [char] (10),
[name] [char] (10)
)insert into #a(id,name) values('11','aaaa')
insert into #a(id,name) values('11','bbbb')
insert into #a(id,name) values('11','cccc')
insert into #a(id,name) values('22','dddd')
insert into #a(id,name) values('22','eeee')
insert into #a(id,name) values('22','ffff') select * from #a b
where name=(select top 1 name from #a where id=b.id)drop table #aid name
---------- ----------
11 aaaa
22 dddd (所影响的行数为 2 行)我对这句话不太理解。那位高手指点迷津啊:)
id name
11 aaaa
22 dddd
CREATE TABLE #a (
[id] [char] (10),
[name] [char] (10)
)insert into #a(id,name) values('11','aaaa')
insert into #a(id,name) values('11','bbbb')
insert into #a(id,name) values('11','cccc')
insert into #a(id,name) values('22','dddd')
insert into #a(id,name) values('22','eeee')
insert into #a(id,name) values('22','ffff') select * from #a b
where name=(select top 1 name from #a where id=b.id)drop table #aid name
---------- ----------
11 aaaa
22 dddd (所影响的行数为 2 行)我对这句话不太理解。那位高手指点迷津啊:)
此时对#a进行查询.
每查一条记录时, where name=(select top 1 name from #a where id=b.id)
即,当前记录的 name等于 select top 1 name from #a where id=当前记录的id
即 在表中查找所有id与当前记录id相同的 第一条记录的 name
select top 1 name from #a where id=b.id 从#a表里面选出和B表对应的ID的第一条name
它返回具有相同id的第一个name,
select * from #a b
where name=...
名字是相同id中的第一个,则选择出来。