select b.* from 表B b,
(
select b.* from 表B b,表a a
where a.username=b.username and a.usermail=b.usermail and a.departmentid=b.departmentidand a.orgid=b.orgid
)a
where a.username<>b.username and a.usermail<>b.usermail and a.departmentid<>b.departmentidand a.orgid<>b.orgid
(
select b.* from 表B b,表a a
where a.username=b.username and a.usermail=b.usermail and a.departmentid=b.departmentidand a.orgid=b.orgid
)a
where a.username<>b.username and a.usermail<>b.usermail and a.departmentid<>b.departmentidand a.orgid<>b.orgid
union
select
'李四','[email protected]', 18, 2
union select
'王五','[email protected]', 18, 2
insert table2 select '张三','[email protected]', 18, 3
union
select
'李四','[email protected]', 18, 2
union select
'王五','[email protected]', 18, 2--测试select b.* from table2 b,
(
--select b.* from table2 b,table1 a
select b.* from table2 b,table1 a
where a.username=b.username and a.usermail=b.usermail and a.departmentid=b.departmentid and a.orgid=b.orgid
)a
where (a.username++a.usermail++cast(a.departmentid as varchar(10))++cast(a.orgid as varchar(10)))<>
( b.username++b.usermail++cast(b.departmentid as varchar(10))++cast(b.orgid as varchar(10)))--结果
username usermail departmentid orgid
-------------------- -------------------- ------------ -----------
王五 [email protected] 18 2
张三 [email protected] 18 3(2 row(s) affected)