表名 telaID 手机号 姓名 1 13177777727 XX 2 13172277727 XX 3 13177337727 XX 4 13177177727 XX表名 telbID 手机号 姓名 1 13277777727 XX 2 13172277727 XX 3 13277337727 XX 4 13177177727 XX 我想输出 表 tela 和 telb 中的手机号。但不能重复的
select distinct 手机号 from tela union select distinct 手机号 from telb
select 手机号 from tela union select 手机号 from telb
--union(除非使用union all)会自动消除重复的 select 手机号 from tela union select 手机号 from telb
select distinct mobile from (select mobile from tela union all select mobile from telb) as s
select distinct mobile from tela union all select distinct mobile from telb b where not exists (select 1 from tela a where a.mobile=b.mobile)
哦?学习了。 好像应该是: select distinct mobile from tela union select distinct mobile from telb
不会的,虽然是一个效果,但是你的语句多了不必要的distinct.
create table tela(ID int,手机号 varchar(20),姓名 varchar(8)) insert into tela select 1,'13177777727','XX' insert into tela select 2,'13172277727','XX' insert into tela select 3,'13177337727','XX' insert into tela select 4,'13177177727','XX' insert into tela select 5,'13177177727','XX'create table telb(ID int,手机号 varchar(20),姓名 varchar(8)) insert into telb select 1,'13277777727','XX' insert into telb select 2,'13172277727','XX' insert into telb select 3,'13277337727','XX' insert into telb select 4,'13177177727','XX' insert into telb select 5,'13177177727','XX'select 手机号 from tela union select 手机号 from telb order by 手机号select distinct 手机号 from tela union --结果一样,亦即在使用union的前提下可以不加distinct select distinct 手机号 from telb order by 手机号 drop table tela,telb; --结果 手机号 -------------------- 13172277727 13177177727 13177337727 13177777727 13277337727 13277777727(6 行受影响)手机号 -------------------- 13172277727 13177177727 13177337727 13177777727 13277337727 13277777727(6 行受影响)
果然不需要distinct,验证正确!
select distinct 手机号 from tela union --结果一样,亦即在使用union的前提下可以不加distinct select distinct 手机号 from telb order by 手机号
select distinct 手机号 from telb
select 手机号 from telb
--union(除非使用union all)会自动消除重复的
select 手机号 from tela
union select 手机号 from telb
from (select mobile from tela
union all
select mobile from telb) as s
union all
select distinct mobile from telb b
where not exists (select 1 from tela a
where a.mobile=b.mobile)
好像应该是:
select distinct mobile from tela
union
select distinct mobile from telb
insert into tela select 1,'13177777727','XX'
insert into tela select 2,'13172277727','XX'
insert into tela select 3,'13177337727','XX'
insert into tela select 4,'13177177727','XX'
insert into tela select 5,'13177177727','XX'create table telb(ID int,手机号 varchar(20),姓名 varchar(8))
insert into telb select 1,'13277777727','XX'
insert into telb select 2,'13172277727','XX'
insert into telb select 3,'13277337727','XX'
insert into telb select 4,'13177177727','XX'
insert into telb select 5,'13177177727','XX'select 手机号 from tela union
select 手机号 from telb
order by 手机号select distinct 手机号 from tela union --结果一样,亦即在使用union的前提下可以不加distinct
select distinct 手机号 from telb
order by 手机号
drop table tela,telb;
--结果
手机号
--------------------
13172277727
13177177727
13177337727
13177777727
13277337727
13277777727(6 行受影响)手机号
--------------------
13172277727
13177177727
13177337727
13177777727
13277337727
13277777727(6 行受影响)
果然不需要distinct,验证正确!
select distinct 手机号 from telb
order by 手机号
你仔细看我举例中的数据了吗,不要想当然.---------insert into tela select 4,'13177177727','XX'
insert into tela select 5,'13177177727','XX'