首頁猿問為什么union前后得出的數(shù)據(jù)并不...

為什么union前后得出的數(shù)據(jù)并不去除重復(fù)的？很多數(shù)據(jù)重復(fù)顯示兩次，求指教！

SQL Server 數(shù)據(jù)結(jié)構(gòu)

躍然一笑 2022-05-11 13:09:16

我用union疊加的結(jié)果有重復(fù)一樣的數(shù)據(jù)（就是同一數(shù)據(jù)顯示兩次）暈，搜了一下，union默認(rèn)就是去除重復(fù)的，而union ALL 才是全部顯示。不知道我的怎么回事，用的是Asp+Access代碼如下：暈啊，distinct我試了，還是重復(fù)，所以問題應(yīng)該不在這。我想用1，2來排序，toadboy的方法實(shí)在是有點(diǎn)復(fù)雜啊。我是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"我是想前后兩個(gè)結(jié)果合并，優(yōu)先顯示name Like '哈哈'的，這點(diǎn)做到了。

查看完整描述

4 回答

開心每一天1111

TA貢獻(xiàn)1836條經(jīng)驗(yàn) 獲得超13個(gè)贊

看不到你寫的代碼，只好猜測(cè)一下：
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"

想法就是：兩個(gè)select語句自己選滿足自己條件的記錄，同時(shí)，去除另一個(gè)select語句選出的記錄

反對(duì) 回復(fù) 2022-05-16

蠱毒傳說

TA貢獻(xiàn)1895條經(jīng)驗(yàn) 獲得超3個(gè)贊

你的結(jié)果集第一個(gè)前面加了個(gè)一第二個(gè)前面加了個(gè)2 那樣肯定不重復(fù)了
比如
從第一個(gè)查詢返回
1 a b c
1 b c d
從第二個(gè)查詢返回
2 a b c
2 b c d

順便問一下你要前面的1,2有什么用去掉不就好了嗎？

反對(duì) 回復(fù) 2022-05-16

幕布斯7119047

TA貢獻(xiàn)1794條經(jīng)驗(yàn) 獲得超8個(gè)贊

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
這個(gè)就是你那條語句，返回結(jié)果為
1 aaa aaa
1 aaa bbb
2 aaa aaa
2 ccc aaa

先在去除重復(fù)的范圍內(nèi)選取，舉例如下：
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
返回結(jié)果：
1 aaa aaa
1 aaa bbb
2 ccc aaa

具體怎么改你的語句，根據(jù)實(shí)際情況來。我這個(gè)例子簡(jiǎn)單，就是用(a+c) not in (select a+c from test1)說明一下去除重復(fù)的范圍。

反對(duì) 回復(fù) 2022-05-16

婷婷同學(xué)_

TA貢獻(xiàn)1844條經(jīng)驗(yàn) 獲得超8個(gè)贊

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"

反對(duì) 回復(fù) 2022-05-16