有2个数据库,一个是用户表a,另外一个是业务表b.我想用1条sql语句显示a,条件是a表在b表中小于5条记录的所有用户比如:
a表有以下10条记录
userid=1,2,3,4,5,6,7,8,9,10
b表有以下记录
userid=1,1,1,1,1,2,2,2,3,3,3,3,3,4,4,4,4,4,5,5,5sql语句的结果应该显示如下记录:
userid=2,5,6,7,8,9,10请问各位大侠这个如何写呢
a表有以下10条记录
userid=1,2,3,4,5,6,7,8,9,10
b表有以下记录
userid=1,1,1,1,1,2,2,2,3,3,3,3,3,4,4,4,4,4,5,5,5sql语句的结果应该显示如下记录:
userid=2,5,6,7,8,9,10请问各位大侠这个如何写呢
from a
inner join (select userid,count(*) as sl from b group by userid) as tb
on a.userid=b.userid and b.sl<5[code=Perl][/code]
from a
inner join (select userid,count(*) as sl from b group by userid) as tb
on a.userid=b.userid and b.sl<5
insert into a select 1 union all select 2 union all select 3
create table b(userid int)
insert into b select 1 union all select 1 union all select 1 union all select 1 union all select 1
insert into b select 2 union all select 2 union all select 2
insert into b select 3
go
select a.userid from a left join b on a.userid=b.userid group by a.userid having count(b.userid)<5
/*
userid
-----------
2
3(2 行受影响)*/
go
drop table a,b
create table a(userid int)
insert into a select 1 union all select 2 union all select 3
create table b(userid int)
insert into b select 1 union all select 1 union all select 1 union all select 1 union all select 1
insert into b select 2 union all select 2 union all select 2
insert into b select 3
select distinct a.userid
from a
inner join (select userid,count(*) as sl from b group by userid) as tb
on a.userid=tb.userid and tb.sl<5
where (select count(1) from b where a.userid=b.userid)<5
a.userid
from
a left join b
on
a.userid=b.userid
group by
a.userid
having
count(b.userid)<5