if exists(select 1 from b group by 字段1 having count(distinct 字段2)=4)
print '存在'
else
print '不存在'
print '存在'
else
print '不存在'
解决方案 »
- 各位兄弟,如何取汉字数字中取出数字?
- 求一个SQL语句
- sqlserver 的数据还原
- 没分了 但还是希望大家帮帮我“数据库附加成功后,里面的表不能识别出来” 奇怪啊
- 0
- 我有一个公网IP,安装上sqlserver2000服务器,其他的机器通过adsl上网,能否访问我的数据库!
- 菜鸟求教:若干表的关联如何建立?
- 重金求子 查询 ,, ############# 100% 结贴
- 拥有SA权限,如何查看某个数据库用户的密码
- 怎么把VFOXPRO做的数据库放到SQL-SERVER中用呢?
- 请问向Binary字段写内容的SQL语句怎样写
- 程数据库,在数据库 'master' 中拒绝了 CREATE DATABASE 权限
(
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中那样的一组数据
*/