比如表A:name subject
--------------------
a 1
a 2
b 1
b 3
b 5
c 1
c 2
d 1
d 3
d 5
e 1
e 2-------------------
姓名对应课程,现在想得到所有姓名,并且与之对应课程完全相同的姓名,如下结果:
name tmpname
---------------------
a c
a e
b d
c a
c e
d b
e a
e c如何通过一条语句查询?
--------------------
a 1
a 2
b 1
b 3
b 5
c 1
c 2
d 1
d 3
d 5
e 1
e 2-------------------
姓名对应课程,现在想得到所有姓名,并且与之对应课程完全相同的姓名,如下结果:
name tmpname
---------------------
a c
a e
b d
c a
c e
d b
e a
e c如何通过一条语句查询?
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (name varchar(1),subject int)
insert into #tb
select 'a',1 union all
select 'a',2 union all
select 'b',1 union all
select 'b',3 union all
select 'b',5 union all
select 'c',1 union all
select 'c',2 union all
select 'd',1 union all
select 'd',3 union all
select 'd',5 union all
select 'e',1 union all
select 'e',2select a.name,b.name
from #tb a,#tb b
where a.subject=b.subject and a.name <>b.name
group by a.name,b.name
having(COUNT(*)>=2)
order by a.name
name name
---- ----
a c
a e
b d
c a
c e
d b
e a
e c(8 row(s) affected)