为什么union前后得出的数据并不去除重复的？很多数据重复显示两次，求指教！

Question

我用union叠加的结果有重复一样的数据（就是同一数据显示两次）晕，搜了一下，union默认就是去除重复的，而union ALL 才是全部显示。不知道我的怎么回事，用的是Asp+Access代码如下：晕啊，distinct我试了，还是重复，所以问题应该不在这。我想用1，2来排序，toadboy的方法实在是有点复杂啊。我是ASP写SQL语句不好写sql="select * from (select 1 as b, data.* from data WHERE name Like '哈哈' union select 2 as b, data.* from data where name Like '%"& 变量 &"%' or tag Like '哈哈') order by b asc"我是想前后两个结果合并，优先显示name Like '哈哈'的，这点做到了。

Answer 1

看不到你写的代码，只好猜测一下：
sql="select * from (select 1 as b, data.* from data WHERE name Like '哈哈' union select 2 as b, data.* from data where name Like '%"& 变量 &"%' or tag Like '哈哈') order by b asc"

改成

sql="
select * from
(select 1 as b, data.*
from data
WHERE
name Like '哈哈'
and name not Like '%"& 变量 &"%'
and tag not Like '哈哈'
union
select 2 as b, data.*
from data
where
name not like'哈哈'
and (name Like '%"& 变量 &"%' or tag Like '哈哈'))
order by b asc"

想法就是：两个select语句自己选满足自己条件的记录，同时，去除另一个select语句选出的记录

Answer 2

你的结果集第一个前面加了个一 第二个前面加了个2 那样肯定不重复了
比如
从第一个查询返回
1 a b c
1 b c d
从第二个查询返回
2 a b c
2 b c d

顺便问一下你要前面的1,2有什么用 去掉不就好了吗？

Answer 3

drop table test1;
drop table test2;
create table test1 (a char(4),c char(4));
create table test2 (a char(4),c char(4));

insert into test1 values('aaa','bbb')
insert into test1 values('aaa','aaa')
insert into test2 values('ccc','aaa')
insert into test2 values('aaa','aaa')

select 1 as b,* from test1 where a like 'aaa'
union select 2 as b,* from test2 where c like 'aaa'
order by b
这个就是你那条语句，返回结果为
1 aaa aaa
1 aaa bbb
2 aaa aaa
2 ccc aaa

先在去除重复的范围内选取，举例如下：
select 1 as b,* from test1 where a like 'aaa'
union select 2 as b,* from test2 where c like 'aaa' and (a+c) not in (select a+c from test1)
order by b
返回结果：
1 aaa aaa
1 aaa bbb
2 ccc aaa

具体怎么改你的语句，根据实际情况来。我这个例子简单，就是用(a+c) not in (select a+c from test1)说明一下去除重复的范围。

 

Answer 4

sql="select distinct * from (select 1 as b, data.* from data WHERE name Like '哈哈') union (select 2 as b, data.* from data where name Like '%"& 变量 &"%' or tag Like '哈哈') order by b asc"