create table a ( num int ) create table b ( s varchar(10),num int ) insert a select 1 union select 2 union select 3 union select 4 insert b select 'a',1 union select 'a',2 union select 'a',3 union select 'a',4 union select 'b',1 union select 'b',2 union select 'c',1 union select 'c',2 union select 'c',3 union select 'c',4 --测试存在性 if exists(select 1 from b group by s having count(distinct num)=(select count(1) from a)) print '存在' else print '不存在'--选择符合条件的记录 select s from b group by s having count(distinct num)=(select count(1) from a)--删除测试环境 drop table a,b--结果 /*存在s ---------- a c(所影响的行数为 2 行) */
比如说哈你表B中的字段1是输出参数,那你该这样写就可以了 @check int out, @stand char(1)select check=count(*) from 表B where 字段2 in (select 字段 from 表A)where 字段1=@stand如果check等于表1中数据条数的话。那就说明是对的。如果不是的话那就是错的哦/ 这是主体。你自己再发挥发挥。小小拙见。 还请高手指正哈
--生成测试数据 create table 表a(字段 int) insert into 表a select 1 insert into 表a select 2 insert into 表a select 3 insert into 表a select 4create table 表b(字段1 char(1),字段2 int) insert into 表b select 'a',1 insert into 表b select 'a',2 insert into 表b select 'a',3 insert into 表b select 'a',4 insert into 表b select 'b',1 insert into 表b select 'b',2 insert into 表b select 'c',1 insert into 表b select 'c',2 insert into 表b select 'c',3 insert into 表b select 'c',4 --执行查询 select b.字段1 from 表a a, 表b b where a.字段 = b.字段2 group by b.字段1 having count(distinct b.字段2) = (select count(distinct 字段) from 表a) --输出结果 字段1 ---- a c
呵呵,如果A是多条,把一楼的4改成你表A的条数就OK了! 如有必要,试试用函数吧!create table a ( num varchar(10) ) create table b ( s varchar(10),num varchar(10) ) insert a select 1 union select 2 union select 3 union select 4 insert b select 'a',1 union select 'a',2 union select 'a',3 union select 'a',4 union select 'b',1 union select 'b',2 union select 'c',1 union select 'c',2 union select 'c',3 union select 'c',4--函數A alter function dbo.fun_a(@a varchar(10)) returns varchar(1000) as begin declare @s varchar(1000) set @s='' select @s=@s+[num]+',' from a return (@s) end--結果 select distinct a=dbo.fun_a(num) into #A from a --函數B alter function dbo.fun_b(@a varchar(10)) returns varchar(1000) as begin declare @s varchar(1000) set @s='' select @s=@s+[num]+',' from b where s=@a return (@s) end --結果 select s, b=dbo.fun_b(s) into #B from b group by s ---差异 select * from #b where exists (select * from #a where #a.a=#b.b) s b ---------- ---------- a 1,2,3,4, c 1,2,3,4,
改: alter function 应为 create functioncreate table a ( num varchar(10) ) create table b ( s varchar(10),num varchar(10) ) insert a select 1 union select 2 union select 3 union select 4 insert b select 'a',1 union select 'a',2 union select 'a',3 union select 'a',4 union select 'b',1 union select 'b',2 union select 'c',1 union select 'c',2 union select 'c',3 union select 'c',4--函數A create function dbo.fun_a(@a varchar(10)) returns varchar(1000) as begin declare @s varchar(1000) set @s='' select @s=@s+[num]+',' from a return (@s) end--結果 select distinct a=dbo.fun_a(num) into #A from a --函數B create function dbo.fun_b(@a varchar(10)) returns varchar(1000) as begin declare @s varchar(1000) set @s='' select @s=@s+[num]+',' from b where s=@a return (@s) end --結果 select s, b=dbo.fun_b(s) into #B from b group by s ---差异 select * from #b where exists (select * from #a where #a.a=#b.b) s b ---------- ---------- a 1,2,3,4, c 1,2,3,4,
create table #a( A1 int) create table #b( B1 varchar(10),B2 int) insert #a select 1 union select 2 union select 3 union select 4 insert #b select 'a',1 union select 'a',2 union select 'a',3 union select 'a',4 union select 'b',1 union select 'b',2 union select 'c',1 union select 'c',2 union select 'c',3 union select 'c',4create procedure chk_Exists_Array @str varchar(10) as if exists( select A.A1 from #a A where not exists (select * from #b B where a.A1 = b.B2 and b.B1 = @str) ) Return 0 --没有表a中那样的一组数据 else Return 1 --有表a中那样的一组数据 godeclare @ReturnValue as int exec @ReturnValue = chk_Exists_Array 'b' if @ReturnValue = 0 print '没有表a中那样的一组数据' else print '有表a中那样的一组数据'/* 没有表a中那样的一组数据 */
(
num int
)
create table b
(
s varchar(10),num int
)
insert a select 1 union select 2 union select 3 union select 4
insert b
select 'a',1 union
select 'a',2 union
select 'a',3 union
select 'a',4 union
select 'b',1 union
select 'b',2 union
select 'c',1 union
select 'c',2 union
select 'c',3 union
select 'c',4
--测试存在性
if exists(select 1 from b group by s having count(distinct num)=(select count(1) from a))
print '存在'
else
print '不存在'--选择符合条件的记录
select s
from b
group by s
having count(distinct num)=(select count(1) from a)--删除测试环境
drop table a,b--结果
/*存在s
----------
a
c(所影响的行数为 2 行)
*/
@check int out,
@stand char(1)select check=count(*) from 表B where 字段2 in (select 字段 from 表A)where 字段1=@stand如果check等于表1中数据条数的话。那就说明是对的。如果不是的话那就是错的哦/
这是主体。你自己再发挥发挥。小小拙见。 还请高手指正哈
create table 表a(字段 int)
insert into 表a select 1
insert into 表a select 2
insert into 表a select 3
insert into 表a select 4create table 表b(字段1 char(1),字段2 int)
insert into 表b select 'a',1
insert into 表b select 'a',2
insert into 表b select 'a',3
insert into 表b select 'a',4
insert into 表b select 'b',1
insert into 表b select 'b',2
insert into 表b select 'c',1
insert into 表b select 'c',2
insert into 表b select 'c',3
insert into 表b select 'c',4
--执行查询
select
b.字段1
from
表a a,
表b b
where
a.字段 = b.字段2
group by
b.字段1
having
count(distinct b.字段2) = (select count(distinct 字段) from 表a)
--输出结果
字段1
----
a
c
如有必要,试试用函数吧!create table a
(
num varchar(10)
)
create table b
(
s varchar(10),num varchar(10)
)
insert a select 1 union select 2 union select 3 union select 4
insert b
select 'a',1 union
select 'a',2 union
select 'a',3 union
select 'a',4 union
select 'b',1 union
select 'b',2 union
select 'c',1 union
select 'c',2 union
select 'c',3 union
select 'c',4--函數A
alter function dbo.fun_a(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[num]+',' from a
return (@s)
end--結果
select distinct a=dbo.fun_a(num) into #A from a --函數B
alter function dbo.fun_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[num]+',' from b where s=@a
return (@s)
end
--結果
select s, b=dbo.fun_b(s) into #B from b group by s
---差异
select * from #b where exists (select * from #a where #a.a=#b.b)
s b
---------- ----------
a 1,2,3,4,
c 1,2,3,4,
(
num varchar(10)
)
create table b
(
s varchar(10),num varchar(10)
)
insert a select 1 union select 2 union select 3 union select 4
insert b
select 'a',1 union
select 'a',2 union
select 'a',3 union
select 'a',4 union
select 'b',1 union
select 'b',2 union
select 'c',1 union
select 'c',2 union
select 'c',3 union
select 'c',4--函數A
create function dbo.fun_a(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[num]+',' from a
return (@s)
end--結果
select distinct a=dbo.fun_a(num) into #A from a --函數B
create function dbo.fun_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[num]+',' from b where s=@a
return (@s)
end
--結果
select s, b=dbo.fun_b(s) into #B from b group by s
---差异
select * from #b where exists (select * from #a where #a.a=#b.b)
s b
---------- ----------
a 1,2,3,4,
c 1,2,3,4,
create table #b( B1 varchar(10),B2 int)
insert #a select 1 union select 2 union select 3 union select 4
insert #b
select 'a',1 union
select 'a',2 union
select 'a',3 union
select 'a',4 union
select 'b',1 union
select 'b',2 union
select 'c',1 union
select 'c',2 union
select 'c',3 union
select 'c',4create procedure chk_Exists_Array
@str varchar(10)
as
if exists(
select A.A1 from
#a A where not exists (select * from #b B where a.A1 = b.B2 and b.B1 = @str)
)
Return 0 --没有表a中那样的一组数据
else
Return 1 --有表a中那样的一组数据
godeclare @ReturnValue as int
exec @ReturnValue = chk_Exists_Array 'b'
if @ReturnValue = 0
print '没有表a中那样的一组数据'
else
print '有表a中那样的一组数据'/*
没有表a中那样的一组数据
*/