求一条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的值
...全文
104 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
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的值

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧