看下这个会不会对你有启发;3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
if exists(select 1 from table1 a join table2 b on a.a=b.a and a.a='aa' and a.b=b.a)
select
b.*
from
table1 a
join
table2 b on a.a=b.a and a.a='aa' and a.b=b.a
else
select
*
from table1 where a='aa'
select
*
from
table2 b
where
not exists(select 1 from table1 a where a='aa' and a=b.a and not exists(select 1 from table2 where a=a.a and b=a.b))
elseselect
*
from
table2 b
where
a='aa' and not exists(select 1 from table1 where a='aa' b=b.b)
if exists(select 1 from table1 a join table2 b on a.a=b.a and a.a= 'aa' and a.b=b.b)
select
*
from
table2 b
where
a='aa' and not exists(select 1 from table1 where a=1 and b=b.b)
elseselect
*
from
table1 b
where
a='aa' and not exists(select 1 from table2 where a='aa' and b=b.b)
但是我在C#.net中怎么知道这个存储过程返回的个呢?
有两个表:
table1
id,A,B
1,aa,bb
2,aa,cc
3,aa,dd
4,aa,ee
5,sa,bb
....
table2
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,3,4,5
3,aa,dd,5,4,3
4,aa,ee,3,6,4
5,ff,ww,2,3,4
6,aa,gg,6,5,3
.....
select * from table1 where A='aa'
select * from table2 where A='aa'
我想实现的功能:
比如上面的A='a'时得到的是
A,B
aa,bb
aa,cc
aa,dd
aa,ee
要在table2中也要找到和table1相同的四列,可以多于
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,3,4,5
3,aa,dd,5,4,3
4,aa,ee,3,6,4
6,aa,gg,6,5,3
要是table2中是下面这样的则不可以
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,3,4,5
3,aa,hh,5,4,3
4,aa,ee,3,6,4
6,aa,gg,6,5,3
要是再加一个条件不知道可以不可以的,table2中存在这样的四列或大于才可以,就是c列也要一样的才行
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,1,4,5
3,aa,dd,1,4,3
4,aa,ee,1,6,4
6,aa,gg,1,5,3
这样不知道能不能明白
select * from table2 where exists(select 1 from table1 where A=table2.A and B.table2.B and C=1)
有两个表:
table1
id,A,B
1,aa,bb
2,aa,cc
3,aa,dd
4,aa,ee
5,sa,bb
....
table2
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,1,4,5
3,aa,dd,1,4,3
4,aa,ee,1,6,4
5,aa,bb,2,3,4
6,aa,cc,2,5,5
7,aa,dd,2,5,5
8,aa,ee,2,5,5
9,ff,ww,2,3,4
10,aa,gg,1,5,3
11,aa,bb,4,5,6
12,aa,cc,3,4,5
13,aa,dd,3,5,5
14,aa,ee,2,3,5
.....
select * from table1 where A='aa'
select * from table2 where A='aa'
我想实现的功能:
比如上面的A='aa'时得到的是
A,B
aa,bb
aa,cc
aa,dd
aa,ee
要在table2中也要找到和table1相同的四列,C列也要相得的四列,可以多于
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,1,4,5
3,aa,dd,1,4,3
4,aa,ee,1,6,4
5,aa,bb,2,3,4
6,aa,cc,2,5,5
7,aa,dd,2,5,5
8,aa,ee,2,5,5
10,aa,gg,1,5,3
返回 C 列 得到 1和2
要是想table2中只有11-14行的数据的话,是不能相得,C列中没有返回
table2
id,A,B,C,D,E
11,aa,bb,4,5,6
12,aa,cc,3,4,5
13,aa,dd,3,5,5
14,aa,ee,2,3,5
.........
不知道这样能不能明白?
老兄,table2的C列为了1的算不算?
写一个自定义函数(dbo.getc(@a)),函数的功能如上所说的,返回C列的内容
select dbo.getc(A) from table1
得到的结果是
1
2
不知道可不可以?
有两个表:
table1
id,A,B
1,aa,bb
2,aa,cc
3,aa,dd
4,aa,ee
5,sa,bb
....
table2
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,1,4,5
3,aa,dd,1,4,3
4,aa,ee,1,6,4
5,aa,bb,2,3,4
6,aa,cc,2,5,5
7,aa,dd,2,5,5
8,aa,ee,2,5,5
9,ff,ww,2,3,4
10,aa,gg,1,5,3
11,aa,bb,4,5,6
12,aa,cc,3,4,5
13,aa,dd,3,5,5
14,aa,ee,2,3,5
.....
select * from table1 where A='aa'
select * from table2 where A='aa'
我想实现的功能:
比如上面的A='aa'时得到的是
A,B
aa,bb
aa,cc
aa,dd
aa,ee
要在table2中也要找到和table1相同的四列,C列也要相得的四列,可以多于
id,A,B,C,D,E
1,aa,bb,1,2,3
2,aa,cc,1,4,5
3,aa,dd,1,4,3
4,aa,ee,1,6,4
5,aa,bb,2,3,4
6,aa,cc,2,5,5
7,aa,dd,2,5,5
8,aa,ee,2,5,5
10,aa,gg,1,5,3
返回 C 列 得到 1和2
要是想table2中只有11-14行的数据的话,是不能相得,C列中没有返回
table2
id,A,B,C,D,E
11,aa,bb,4,5,6
12,aa,cc,3,4,5
13,aa,dd,3,5,5
14,aa,ee,2,3,5
.........
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[A] nvarchar(2),[B] nvarchar(2))
Insert #T1
select 1,N'aa',N'bb' union all
select 2,N'aa',N'cc' union all
select 3,N'aa',N'dd' union all
select 4,N'aa',N'ee' union all
select 5,N'sa',N'bb'
Go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[A] nvarchar(2),[B] nvarchar(2),[C] int,[D] int,[E] int)
Insert #T2
select 1,N'aa',N'bb',1,2,3 union all
select 2,N'aa',N'cc',1,4,5 union all
select 3,N'aa',N'dd',1,4,3 union all
select 4,N'aa',N'ee',1,6,4 union all
select 5,N'aa',N'bb',2,3,4 union all
select 6,N'aa',N'cc',2,5,5 union all
select 7,N'aa',N'dd',2,5,5 union all
select 8,N'aa',N'ee',2,5,5 union all
select 10,N'aa',N'gg',1,5,3
Go
if not exists(select * from #t1 b where a='aa' and not exists(select 1 from #t2 where a=b.a and b=b.b))
select
*
from
#t2 a
where
a='aa' and not exists(select * from #t1 b where a=a.a and not exists(select 1 from #t2 where a=b.a and b=b.b))
else
select * from #t1 b where a='aa' and not exists(select 1 from #t2 where a=b.a and b=b.b)
/*
id A B C D E
----------- ---- ---- ----------- ----------- -----------
1 aa bb 1 2 3
2 aa cc 1 4 5
3 aa dd 1 4 3
4 aa ee 1 6 4
5 aa bb 2 3 4
6 aa cc 2 5 5
7 aa dd 2 5 5
8 aa ee 2 5 5
10 aa gg 1 5 3(9 行受影响)*/
--aa改为sa
if not exists(select * from #t1 b where a='sa' and not exists(select 1 from #t2 where a=b.a and b=b.b))
select
*
from
#t2 a
where
a='sa' and not exists(select * from #t1 b where a=a.a and not exists(select 1 from #t2 where a=b.a and b=b.b))
else
select * from #t1 b where a='sa' and not exists(select 1 from #t2 where a=b.a and b=b.b)/*
id A B
----------- ---- ----
5 sa bb(1 行受影响)*/------------
当='aa'时返回的是1和2,就是TABLE2的C列啊,
当='sa'时是没有返回数据的,因为TABLE2中没有和TABLE1相应的数据啊
distinct C--这样就行了
from
#t2 a
where
a='aa' and not exists(select * from #t1 b where a=a.a and not exists(select 1 from #t2 where a=b.a and b=b.b))
drop table #T1
Go
Create table #T1([id] int,[A] nvarchar(2),[B] nvarchar(2))
Insert #T1
select 1,N'aa',N'bb' union all
select 2,N'aa',N'cc' union all
select 3,N'aa',N'dd' union all
select 4,N'aa',N'ee' union all
select 5,N'sa',N'bb'
Go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[A] nvarchar(2),[B] nvarchar(2),[C] int,[D] int,[E] int)
Insert #T2
select 1,N'aa',N'bb',1,2,3 union all
select 2,N'aa',N'cc',1,4,5 union all
select 3,N'aa',N'dd',1,4,3 union all
select 4,N'aa',N'ee',3,6,4 union all '把这个的C列改成3了数据返回3个结果"1,2,3",实际上要是这样的话只有2的,现在有3个,就不对了
select 5,N'aa',N'bb',2,3,4 union all
select 6,N'aa',N'cc',2,5,5 union all
select 7,N'aa',N'dd',2,5,5 union all
select 8,N'aa',N'ee',2,5,5 union all
select 10,N'aa',N'gg',1,5,3
Go
if not exists(select * from #t1 b where a='aa' and not exists(select 1 from #t2 where a=b.a and b=b.b))
select
distinct C--这样就行了
from
#t2 a
where
a='aa' and not exists(select * from #t1 b where a=a.a and not exists(select 1 from #t2 where a=b.a and b=b.b))
else
select * from #t1 b where a='aa' and not exists(select 1 from #t2 where a=b.a and b=b.b)