select * from test where ( proid=1 or proid=2 ) and testname=testname
proid 有可能出入的是多个参数 要查找出传入参数中有共同信息相同的testname信息
比如说:select testname from test where proid=1 select testname from test where proid=2 这两条语句中相同的信息
proid: testname 1 aa 1 bb 1 rr 2 aa 2 bb 2 cc最后得到的数据是 aa, bb
--> 测试数据: #tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (proid int,testname varchar(2)) insert into #tb select 1,'aa' union all select 1,'bb' union all select 1,'rr' union all select 2,'aa' union all select 2,'bb' union all select 2,'cc'select a.testname from #tb a,#tb b where a.testname=b.testname and a.proid=1 and b.proid=2 testname -------- aa bb(2 row(s) affected)
DECLARE @tb TABLE(proid int,testname varchar(2)) INSERT into @tb select 1,'aa' union all select 1,'bb' union all select 1,'rr' union all select 2,'aa' union all select 2,'bb' union all select 2,'cc'select a.testname, a.proid, b.proid from @tb a inner join @tb b on a.testname=b.testname and a.proid < b.proid /* testname proid proid -------- ----------- ----------- aa 1 2 bb 1 2*/
不过1,2只是一个参数而已 有可能传入多个参数? 能不能用 where proid in (1,2,...) 呢
create table #tryT(id int ,testname nvarchar(10),proid int) insert #tryT select 1,'aa',1 union all select 2,'bb',1 union all select 3,'rr',1 union all select 4,'aa',2union all select 5,'bb',2 union all select 6,'cc',2 declare @pid1 as int declare @pid2 as int set @pid1=3 set @pid2=2 select t1.testname from #tryT as t1 join #tryT as t2 on t1.testname=t2.testname and t1.proid=@pid1 and t2.proid=@pid2 改@pid1和@pid2应该可以了..
if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (proid int,testname varchar(2)) insert into #tb select 1,'aa' union all select 1,'bb' union all select 1,'rr' union all select 2,'aa' union all select 2,'bb' union all select 2,'cc'select testname from #tb where proid in(1,2) --可能更多 group by testname having(COUNT(distinct proid)=2) --N与传入的参数个数相同testname -------- aa bb(2 row(s) affected)
select id ,testname,proid from tb where testname in(select testname group by tb having count(1)>1)
表中可能出现这样的数据吗? proid: testname 1 aa 1 bb 1 rr 1 aa 2 bb 2 cc
create table tb (proid int,testname varchar(2)) insert into tb select 1,'dd' union all select 1,'bb' union all select 1,'rr' union all select 2,'aa' union all select 2,'bb' union all select 3,'aa' union all select 1,'rr' 如果希望出现结果 aa bb select distinct(testname) from tb where testname in (select testname from tb group by testname having count(1)>1) and testname not in (select testname from tb group by testname,proid having count(1)>1)需要知道proid号 select testname,proid from tb where testname in (select testname from tb group by testname having count(1)>1) and testname not in (select testname from tb group by testname,proid having count(1)>1) order by testname
要查找出传入参数中有共同信息相同的testname信息
select testname from test where proid=2
这两条语句中相同的信息
1 aa
1 bb
1 rr
2 aa
2 bb
2 cc最后得到的数据是 aa, bb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (proid int,testname varchar(2))
insert into #tb
select 1,'aa' union all
select 1,'bb' union all
select 1,'rr' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc'select a.testname from #tb a,#tb b
where a.testname=b.testname
and a.proid=1 and b.proid=2
testname
--------
aa
bb(2 row(s) affected)
INSERT into @tb
select 1,'aa' union all
select 1,'bb' union all
select 1,'rr' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc'select a.testname, a.proid, b.proid
from @tb a
inner join @tb b
on a.testname=b.testname
and a.proid < b.proid
/*
testname proid proid
-------- ----------- -----------
aa 1 2
bb 1 2*/
能不能用 where proid in (1,2,...)
呢
create table #tryT(id int ,testname nvarchar(10),proid int)
insert #tryT select 1,'aa',1 union all
select 2,'bb',1 union all
select 3,'rr',1 union all
select 4,'aa',2union all
select 5,'bb',2 union all
select 6,'cc',2
declare @pid1 as int
declare @pid2 as int
set @pid1=3
set @pid2=2
select t1.testname from #tryT as t1 join #tryT as t2
on t1.testname=t2.testname and t1.proid=@pid1 and t2.proid=@pid2
改@pid1和@pid2应该可以了..
go
create table #tb (proid int,testname varchar(2))
insert into #tb
select 1,'aa' union all
select 1,'bb' union all
select 1,'rr' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc'select testname from #tb
where proid in(1,2) --可能更多
group by testname
having(COUNT(distinct proid)=2) --N与传入的参数个数相同testname
--------
aa
bb(2 row(s) affected)
where testname in(select testname group by tb having count(1)>1)
proid: testname
1 aa
1 bb
1 rr
1 aa
2 bb
2 cc
insert into tb
select 1,'dd' union all
select 1,'bb' union all
select 1,'rr' union all
select 2,'aa' union all
select 2,'bb' union all
select 3,'aa' union all
select 1,'rr' 如果希望出现结果
aa
bb
select distinct(testname) from tb
where testname in
(select testname from tb group by testname having count(1)>1)
and testname not in
(select testname from tb group by testname,proid having count(1)>1)需要知道proid号
select testname,proid from tb
where testname in
(select testname from tb group by testname having count(1)>1)
and testname not in
(select testname from tb group by testname,proid having count(1)>1)
order by testname