日期:2014-05-18  浏览次数:20555 次

看似简单,一下你搞不定
两张表
TA
UID           UNAME
1               A
2               B
3               C
4               D
--------------------
TB
UID           UNAME
1               A
2               BB
5               E
6               F
现在要求查出TA.UID <> TB.UID   并且   TA.UNAME <> TB.UNAME的TA数据
结果应该如下
UID           UNAME
2               B
3               C
4               D
查询语句如何写?

------解决方案--------------------
晕倒,select TA.UID,TA.UNAME FROM TA ,TB WHERE TA.UID <> TB.UID AND TA.UNAME <> TB.UNAME
OR
SELECT TA.UID,TA.UNAME FROM TA WHERE TA.UID NOT IN (SELECT TA.UID FROM TA JOIN TB ON TA.UID=TB.UID,TA.UNAME=TB.UNAME)
------解决方案--------------------
现在要求查出TA.UID <> TB.UID 并且 TA.UNAME <> TB.UNAME的TA数据
结果应该如下
UID UNAME
2 B
3 C
4 D
不是吧 应该是 3 c
4 d 吧
select * from ta
join tb on ta.uid <> tb.uid and ta.uname <> tb.uname 不行吗
------解决方案--------------------
忽悠人哦
------解决方案--------------------
create table TA
(
UID int,
UNAME varchar(2)
)

create table TB
(
UID int,
UNAME varchar(2)
)

insert TA select 1, 'A '
insert TA select 2, 'B '
insert TA select 3, 'C '
insert TA select 4, 'D '

insert TB select 1, 'A '
insert TB select 2, 'BB '
insert TB select 5, 'E '
insert TB select 6, 'F '


select TA.*
from TA
where not exists (select 1 from TB where cast(TA.UID as varchar) + TA.UNAME=cast(TB.UID as varchar)+TB.UNAME)

------解决方案--------------------
借楼上的表数据:
create table TA
(
UID int,
UNAME varchar(2)
)

create table TB
(
UID int,
UNAME varchar(2)
)

insert TA select 1, 'A '
insert TA select 2, 'B '
insert TA select 3, 'C '
insert TA select 4, 'D '

insert TB select 1, 'A '
insert TB select 2, 'BB '
insert TB select 5, 'E '
insert TB select 6, 'F '

SELECT TA.* FROM TA WHERE NOT EXISTS(SELECT 1 FROM TB WHERE TB.UID=TA.UID AND TB.UNAME=TA.UNAME)

DROP TABLE TA,TB

/*
UID UNAME
2 B
3 C
4 D
*/

------解决方案--------------------
对了,
接分
------解决方案--------------------
搞不定!!
------解决方案--------------------
结论:不可能搞定!!!
------解决方案--------------------
现在要求查出TA.UID <> TB.UID 并且 TA.UNAME <> TB.UNAME的TA数据