向各位大哥请教一条SQL语句

[xin51job]

表bevy
bevy_id bevy_name
1 足球
3 蓝球
表bevyman
bevyman_id bevy_id man_id
1 1 1
2 1 2
3 3 1
1 3 5
当man_id=1时,如何得到
bevy_id man_id
1 2
3 5
谢谢大家

[jinjazz]

貌似是select * from bevyman where man_id<>1吧

[kk19840210]

select bevy_id,man_id from bevyman where man_id <> 1

[-狙击手-]

select a.bevy_id,main_id
from bevyman a
left join bevy b
on a.bevy_id = b.bevy_id 
where a.man_id <> 1

[free1879]

SELECT bevy_id,man_id
FROM bevyman
WHERE man_id <> 1

[xin51job]

谢谢大家,是这样的.
表bevy
bevy_id bevy_name
1 足球
2 蓝球
3 台球
表bevyman
bevyman_id bevy_id man_id
1 1 1
2 1 2
3 3 1
4 3 5
5 2 3
当man_id=1时,如何得到
bevy_id man_id
1 2
3 5
得到与man_id=1相同的bevy_id的另一man_id

[jinjazz]

--建立测试环境
set nocount on
create table test(bevyman_id varchar(20),bevy_id varchar(20),man_id varchar(20))
insert into test select '1','1','1'
insert into test select '2','1','2'
insert into test select '3','3','1'
insert into test select '4','3','5'
insert into test select '5','2','3'
go
--测试
select * from test a where exists(select 1 from
test where man_id=1 and bevy_id=a.bevy_id and man_id<>a.man_id)
--删除测试环境
drop table test
set nocount off

[yuedeem]

select * from bevyman where bevy_id in (select bevy_id from bevyman where man_id=1)