我目前在开发微软CRM4.0的报表,用select语句的时候发现不能用NOT IN 和NOT exists 关键字了,用这2个就搜不到任何数据。现在碰到了一个需求不用NOT IN不知道怎么解决了。
有2个表
A表 B表
b_id id name
1 a
1 b
2 a现在的需求是找出B表中name等于a,并且只等于a的id,然后根据B表的id来搜索A表的数据,本来用select * from a where a.b_id not in (select b.id from b where b.name <> a)能解决,现在不能用NOT IN就好像没办法解决了,各位大哥帮帮忙啊。
有2个表
A表 B表
b_id id name
1 a
1 b
2 a现在的需求是找出B表中name等于a,并且只等于a的id,然后根据B表的id来搜索A表的数据,本来用select * from a where a.b_id not in (select b.id from b where b.name <> a)能解决,现在不能用NOT IN就好像没办法解决了,各位大哥帮帮忙啊。
-- ---华夏小卒---
-- --向大虾们靠拢--- 5 points per day
-- -----------------------------select * from a where a.b_id in (select b.id from b where b.name = a)
select * from a where exists(
select distinct 1 from b
where a.b_id=b.id
group by b.id having count(1)=1
)
你这个语句本身有问题吧,b.name <> a?
select a.b_id from b left join a on b.b_id=a.id where a.b_id is null
代替 in
而且可以解决in不支持多字段的弊端(既语法不支持:(f1,f2,...) in (select f1,f2,... from ...))用from a left join b on a.f1=b.f1 and a.f2=b.f2 ... where b.f1 is null
代替not in
create table b(
id int,
[name] varchar(10)
)
create table a
(
b_id int
)
insert into b(id,[name])
select 1,'a' union all
select 1,'b' union all
select 3,'a' union all
select 4,'c' union all
select 2,'a'
insert into a
select 1 union all
select 2 union all
select 3select * from a where exists(
select distinct 1 from b
where a.b_id=b.id
group by b.id having count(1)=1
)drop table a
drop table b
/*
b_id
2
3
*/
可能是
b表
id name
1 a
1 a
1 b
1 c
2 a
3 b
4 c
4 a
要搜出b表name等于a的值,等于别的就不行的
id name
1 a
1 a
1 b
1 c
2 a
3 b
4 c
4 a 我想搜出的结果是id=2,其他的name 都有别的值了。我就想搜出name等于a 的记录啊,然后再搜索a表
create table b(
id int,
[name] varchar(10)
)
create table a
(
b_id int
)
insert into b(id,[name])
select 1,'a' union all
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 3,'b' union all
select 4,'c' union all
select 4,'a'
insert into a
select 1 union all
select 2 union all
select 3select * from a where exists(
select distinct 1 from b
where a.b_id=b.id and b.[name]='a'
group by b.id having count(1)=1
)drop table a
drop table b
/*
b_id
2不知道你到底想要什么
*/