求一sql语句.

Question

table1id needobject_id need_count
1      16            10
2      17            20talbe2id object_id holdcounts
1      16         100
2      17          10
3      18         20 判断table1中的needobject_id是否都包含在talbe2中,并且holdcounts大于need_count,不用游标是否可以实现..

Answer 1

select * from table1 where needobject_id not in (select object_id from table2)
union all
select a.* from table1 a,table2 b where a.needobject_id = b.object_id and a.need_count <= b.holdcounts

Answer 2

if object_id('pubs..table1') is not null
   drop table table1
gocreate table table1
(
id int,
needobject_id int,
need_count int
)insert into table1(id,needobject_id,need_count) values(1,      16,            10)
insert into table1(id,needobject_id,need_count) values(2,      17,            20)if object_id('pubs..table2') is not null
   drop table table2
gocreate table table2
(
id int,
object_id int,
holdcounts int
)insert into table2(id,object_id,holdcounts) values(1,      16,         100)
insert into table2(id,object_id,holdcounts) values(2,      17,         10)
insert into table2(id,object_id,holdcounts) values(3,      18,         20)select * from table1 where needobject_id not in (select object_id from table2)
union all
select a.* from table1 a,table2 b where a.needobject_id = b.object_id and a.need_count <= b.holdcounts
drop table table1,table2/*result
id          needobject_id need_count  
----------- ------------- ----------- 
1           16            10（所影响的行数为 1 行）
*/

Answer 3

select table1.* from table1 inner join table2 on table1.needobject_id=table2.object_id and table1.need_count<table2.need_count

Answer 4

if object_id('pubs..table1') is not null
   drop table table1
gocreate table table1
(
id int,
needobject_id int,
need_count int
)insert into table1(id,needobject_id,need_count) values(1,      16,            10)
insert into table1(id,needobject_id,need_count) values(2,      17,            20)if object_id('pubs..table2') is not null
   drop table table2
gocreate table table2
(
id int,
object_id int,
holdcounts int
)insert into table2(id,object_id,holdcounts) values(1,      16,         100)
insert into table2(id,object_id,holdcounts) values(2,      17,         10)
insert into table2(id,object_id,holdcounts) values(3,      18,         20)select case when count(*) > 0 then 0 else 1 end as value from
(
  select * from table1 where needobject_id not in (select object_id from table2)
  union all
  select a.* from table1 a,table2 b where a.needobject_id = b.object_id and a.need_count <= b.holdcounts
) tdrop table table1,table2value       
----------- 
0（所影响的行数为 1 行）