select * from 表A a inner join 表A b on a.clientname = b.clientname and a.clientname= b.type
SELECT * FROM 表A T WHERE EXISTS(SELECT 1 FROM 表A WHERE type=T.type and clientname=T.clientname)
改进下create table tableA ( clientname nvarchar(100), type nvarchar(10) ) insert tableA select 1,1 union all select 1,1 union select 2,1--select * from tableAselect a.clientname,a.type from tableA a inner join tableA b on a.clientname = b.clientname and a.clientname= b.type drop table tableA 当然还可以用其他的方法解决这个问题
create table #tb(clientname varchar(20),type varchar(20)) insert into #tb select 'aaa','bbbb'insert into #tb select 'ccc','aaa' insert into #tb select '1','ccc' insert into #tb select '1','ddd' select clientname from #tb where exists(select*from #tb a where a.type=tb.clientname) select type from #tb where exists(select*from #tb a where a.clientname=tb.type)
create table #tb(clientname varchar(20),type varchar(20)) insert into #tb select 'aaa','bbbb'insert into #tb select 'ccc','aaa' insert into #tb select '1','ccc' insert into #tb select '1','ddd' select clientname from #tb where exists(select*from #tb a where a.type=#tb.clientname) select type from #tb where exists(select*from #tb a where a.clientname=#tb.type)
select * from ta a where exists(select 1 from ta b where a.clientname=b.type)
select clientname, type,count(*) as 重复个数 from 表A group clientname, type having count(*) > 1
create table tableA( clientname nvarchar(10), type nvarchar(10) )insert tableA select 1,1 union select 1,1 union select 2,1select a.clientname,a.type from tableA a where exists( select * from tableA b where a.clientname = b.clientname and a.type= b.type group by clientname,type having count(*)>1)
inner join 表A b
on a.clientname = b.clientname
and a.clientname= b.type
改进下create table tableA
(
clientname nvarchar(100),
type nvarchar(10)
)
insert tableA select 1,1 union all select 1,1 union select 2,1--select * from tableAselect a.clientname,a.type from tableA a
inner join tableA b
on a.clientname = b.clientname
and a.clientname= b.type
drop table tableA
当然还可以用其他的方法解决这个问题
insert into #tb select 'aaa','bbbb'insert into #tb select 'ccc','aaa'
insert into #tb select '1','ccc'
insert into #tb select '1','ddd'
select clientname from #tb where exists(select*from #tb a where a.type=tb.clientname)
select type from #tb where exists(select*from #tb a where a.clientname=tb.type)
insert into #tb select 'aaa','bbbb'insert into #tb select 'ccc','aaa'
insert into #tb select '1','ccc'
insert into #tb select '1','ddd'
select clientname from #tb where exists(select*from #tb a where a.type=#tb.clientname)
select type from #tb where exists(select*from #tb a where a.clientname=#tb.type)
where exists(select 1 from ta b where a.clientname=b.type)
group clientname, type
having count(*) > 1
create table tableA(
clientname nvarchar(10),
type nvarchar(10)
)insert tableA select 1,1 union select 1,1 union select 2,1select a.clientname,a.type
from tableA a
where exists(
select * from tableA b
where a.clientname = b.clientname and a.type= b.type
group by clientname,type
having count(*)>1)
--------------
1 2 3 4
2 2 3 4
3 3 2 1
4 5 3 4
5 6 3 6
6 2 2 4我要找出b,d2个字段相同的所有记录运行语句后,结果为
1 2 3 4
2 2 3 4
6 2 2 4该怎么实现啊
--------------
1 2 3 4
2 2 3 4
3 3 2 1
4 5 3 4
5 6 3 6
6 2 2 4我要找出b,d2个字段相同的所有记录运行语句后,结果为
1 2 3 4
2 2 3 4
6 2 2 4该怎么实现啊
where (select count(*) from tb where b=a.b and d=a.d)>1
where exists
(
select * from t1 b where a.type=b.type and a.d=b.d
having count(*)>1
)