两个数据结果放在一起 union all一定要用的。
过滤重复数据distinct貌似也不能少。能优化的估计就是这个not exists了。;with maco as
(
select t1.UserID 'ID', t1.UserName 'Name',t2.UserPhone 'PHONE'
from T_User t1 join T_UserInfo t2 on t1.UserID=t2.UserID
union all
select t1.UserID 'ID',t1.UserName 'Name',t2.UserPhone 'PHONE'
from U_User t1 join U_UserInfo t2
on t1.UserID=t2.UserID
)
select distinct a.* from maco a left join UserPHONE b
on a.PHONE=b.pnum where b.pid is null
/*
ID Name PHONE
----------- -------------------- --------------------
2 B 13500000002
3 C 13500000003
5 E 13500000005
6 F 13500000006
8 H 13500000008
9 I 13500000009
10 J 13500000010
*/
过滤重复数据distinct貌似也不能少。能优化的估计就是这个not exists了。;with maco as
(
select t1.UserID 'ID', t1.UserName 'Name',t2.UserPhone 'PHONE'
from T_User t1 join T_UserInfo t2 on t1.UserID=t2.UserID
union all
select t1.UserID 'ID',t1.UserName 'Name',t2.UserPhone 'PHONE'
from U_User t1 join U_UserInfo t2
on t1.UserID=t2.UserID
)
select distinct a.* from maco a left join UserPHONE b
on a.PHONE=b.pnum where b.pid is null
/*
ID Name PHONE
----------- -------------------- --------------------
2 B 13500000002
3 C 13500000003
5 E 13500000005
6 F 13500000006
8 H 13500000008
9 I 13500000009
10 J 13500000010
*/
(
UserID int,
UserName varchar(20),
userLevel int
)
CREATE TABLE T_UserInfo
(
UserID int,
UserPhone varchar(20),
userQQ int
)
如果是按照你写的表结构的话 好像没必要分这两个表吧?
整合成一个表就好了
select distinct a.userid, a.username, b.userphone
from t_user a
left join t_userinfo b on a.userid = b.userid
left join userphone c on b.userphone = c.pnum
where c.pnum is null
还可以减少点逻辑读
但是4个表 水平有限 改不出什么来
另外建议在相应字段加索引
收藏等待高手