为什么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 dataWHEREname Like '哈哈'and name not Like '%"& 变量 &"%'and tag not Like '哈哈'unionselect 2 as b, data.*from datawherename not like'哈哈'and (name Like '%"& 变量 &"%' or tag Like '哈哈'))order by b asc"想法就是：两个select语句自己选满足自己条件的记录，同时，去除另一个select语句选出的记录

Answer 2

你的结果集第一个前面加了个一 第二个前面加了个2 那样肯定不重复了比如从第一个查询返回1 a b c1 b c d从第二个查询返回2 a b c2 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 aaa1 aaa bbb2 aaa aaa2 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 aaa1 aaa bbb2 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"