select
a.serid as A业务号,a.name as A姓名,a.title AS A说明,
a.b_serid AS B业务号,b.name AS B姓名, b.title AS B说明
from test a,test b
where a.b_serid =b.serid
and a.serid < a.b_serid
a.serid as A业务号,a.name as A姓名,a.title AS A说明,
a.b_serid AS B业务号,b.name AS B姓名, b.title AS B说明
from test a,test b
where a.b_serid =b.serid
and a.serid < a.b_serid
a.serid as A业务号,a.name as A姓名,a.title AS A说明,
a.b_serid AS B业务号,b.name AS B姓名, b.title AS B说明
from test a,test b
where a.b_serid =b.serid
and a.serid + '' + a.b_serid
not exists(select ina.b_serid + '' + ina.serid
from test ina
where ina.serid = b.serid )
这样去理解是吗!?
create table A
(
id int,
T_name varchar(10),
T_txt varchar(10),
T_id int
)insert A select 1,'kk','cc',3
insert A select 2,'kk1','cc1',4
insert A select 3,'er1','sds',1
insert A select 4,'sdf','34s',2
insert A select 5,'sdf','34s',0select * from
(select T1.id as T1_id ,T1.T_name as T1_T_name,T1.T_txt as T1_T_txt,T2.id as T2_id,T2.T_name as T2_T_name,T2.T_txt as T2_T_txt
from A T1,A T2
where T1.T_id=T2.id) T
where T1_id<T2_id
union
select T1.id,T1.T_name,T1.T_txt,0 as id,'' as T_name,'' as T_txt
from A T1
where T1.id not in(select Tn1.id from A Tn1,A Tn2 where Tn1.T_id =Tn2.id)
谢谢老大的回复,但我却不知其所以然。主要是这句: and a.serid < a.b_serid???何意。
a.serid as A业务号,a.name as A姓名,a.title AS A说明,a.b_serid AS B业务号,b.name AS B姓名, b.title AS B说明
from test a,test b
where a.b_serid =b.serid
and a.serid < a.b_serid A业务号 A姓名 A说明 B业务号 B姓名 B说明
123 KK CC 456 BB AAA
456 BB AAA 123 KK CCC
为了不让出现这种重复,用a.serid < a.b_serid (大于也行啦)去除重复
123 KK CC 456 BB AAA
456 BB AAA 123 KK CCC从这个数据可以看到, A业务号和B业务号互换导致重复, 所以过滤一下