去除重复查询SQL------

reallylovesky 2008-06-20 03:07:08
查询出最大的NO和其他数据,以为join 到很多表,但是子查询没有发 orderby 和Group by
Id NO Data
1 1 a
1 2 b
2 1 b
2 2 a
2 3 d

table B

Id Name
1 zhang
2 zou
table c
id age
1 50
2 50


查询结果为
name Id NO data age
zhang 1 2 b 50
zou 2 3 d 50
...全文
78 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
reallylovesky 2008-06-20
  • 打赏
  • 举报
回复
谢谢大家了!!!!
刚才忘了!!!
utpcb 2008-06-20
  • 打赏
  • 举报
回复
select name,a.id,no,data,age
from
(select * from ta e where not exists(select 1 from ta where id = e.id and no > e.no)) a
left join
tb b on a.id = b.id
left join
tc c on a.id = c.id
-狙击手- 2008-06-20
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 happyflystone 的回复:]
SQL codeselect name,a.id,no,data,age
from
(select * from ta e where not exists(select 1 from ta where id = e.id and no > e.no) a
left join
tb b on a.id = b.id
left join
tc c on a.id = c.id
[/Quote]

少一个括号

select name,a.id,no,data,age
from
(select * from ta e where not exists(select 1 from ta where id = e.id and no > e.no)) a
left join
tb b on a.id = b.id
left join
tc c on a.id = c.id
pt1314917 2008-06-20
  • 打赏
  • 举报
回复

--> 测试数据: @s
declare @s table (Id int,NO int,Data varchar(1))
insert into @s
select 1,1,'a' union all
select 1,2,'b' union all
select 2,1,'b' union all
select 2,2,'a' union all
select 2,3,'d'
--> 测试数据: @B
declare @B table (Id int,Name varchar(5))
insert into @B
select 1,'zhang' union all
select 2,'zou'
--> 测试数据: @c
declare @c table (id int,age int)
insert into @c
select 1,50 union all
select 2,50


select name,b.id,a.no,a.data,age from @b b
left join (select * from @s a where not exists(select 1 from @s where id=a.id and no>a.no))a
on a.id=b.id
left join @c c
on a.id=c.id
flairsky 2008-06-20
  • 打赏
  • 举报
回复
更正
where t.id=b.id and t.id=c.id
-狙击手- 2008-06-20
  • 打赏
  • 举报
回复
select name,a.id,no,data,age
from
(select * from ta e where not exists(select 1 from ta where id = e.id and no > e.no) a
left join
tb b on a.id = b.id
left join
tc c on a.id = c.id
flairsky 2008-06-20
  • 打赏
  • 举报
回复
select b.name,t.no,t.data,c.age from b,c,(select id,data,max(no) as no from A group by id,data) as t
where a.id = b.id and a.id = c.id

34,571

社区成员

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

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