导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一条sql语句

bitm 2008-01-18 02:49:36
如下两个表:
表1
table1

attachid name
100 北京
200 上海
null ....


表2
table2

aid attachid content
1 200 test
3 100 sdfsdf
4 100  3212
5 100 dsd

table1 跟table2是一对多的关系。。attachid 为两表关链键,table1中attachid为唯一值

如果我用这样的语句:
select * from table1 left join table2 on table1.attachid= table2.attachid
就会查询出多条attachid相同值的记录。如何排出这些值?我只想取一条attachid的值
...全文
46 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
marco08 2008-01-18

--result
attachid name aid attachid content
----------- ---------- ----------- ----------- ----------
100 北京 5 100 dsd
200 上海 1 200 test

(所影响的行数为 2 行)

回复
dawugui 2008-01-18

--max(aid)
select m.* , n.* from table1 m,
(
select t.* from table2 t where aid = (select max(aid) from table2 where attchid = t.attchid)
) n
where m.attachid = n.attachid

--min(aid)
select m.* , n.* from table1 m,
(
select t.* from table2 t where aid = (select min(aid) from table2 where attchid = t.attchid)
) n
where m.attachid = n.attachid

回复
wzy_love_sly 2008-01-18
declare @a table (attachid int,name varchar(10))
insert into @a select 100,'北京'
insert into @a select 200,'上海'

declare @b table (aid int,attachid varchar(10),content varchar(10))
insert into @b select 1,200,'test'
insert into @b select 3,100,'sdfsdf'
insert into @b select 4,100,'3212'
insert into @b select 5,100,'dsd'

select * from @a a
left join @b b on a.attachid=b.attachid
where not exists(
select 1 from @b where attachid=b.attachid and aid>b.aid
)


attachid name aid attachid content
100 北京 5 100 dsd
200 上海 1 200 test
回复
marco08 2008-01-18

create table A(
attachid int,
[name] nvarchar(10)
)
insert A select 100,'北京'
insert A select 200,'上海'

create table B(
aid int,
attachid int,
content nvarchar(10)
)
insert B select 1,200,'test'
insert B select 3,100,'sdfsdf'
insert B select 4,100,'3212'
insert B select 5,100,'dsd'

select * from A
left join (
select * from B as tmp
where not exists(select * from B where attachid=tmp.attachid and aid>tmp.aid)
)B on A.attachid=B.attachid
回复
kk19840210 2008-01-18
select   distinct table1.*   from   table1   left   join   table2   on   table1.attachid=   table2.attachid 
回复
dennis80 2008-01-18
distinct
回复
liangCK 2008-01-18
select a.name,b.aid,b.attachid,b.content
from table1 a left join table2 b
on a.attachid=b.attachid
group by a.name,b.aid,b.attachid,b.content
回复
bitm 2008-01-18
补充:table1中,如果attachid有值,那么就是唯 一值,也有为null的值
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告