比如表1的字段1中的记录如下:
aaa
bbb
ccc
ddd表2的字段1中的记录如下:
aaa
bbb
ccc
ddd
ddd
eee这个SQL既要把表2中多的eee挑出来,也要把重复的ddd也挑出来。
最后的结果是
ddd
eee我用select * from x_tmp2 t where t.id not in (select t1.id from x_tmp1 t1)
和select * from x_tmp2 minus select * from x_tmp1都不行,只能挑出来eee,但是重复的ddd挑不出来。
aaa
bbb
ccc
ddd表2的字段1中的记录如下:
aaa
bbb
ccc
ddd
ddd
eee这个SQL既要把表2中多的eee挑出来,也要把重复的ddd也挑出来。
最后的结果是
ddd
eee我用select * from x_tmp2 t where t.id not in (select t1.id from x_tmp1 t1)
和select * from x_tmp2 minus select * from x_tmp1都不行,只能挑出来eee,但是重复的ddd挑不出来。
union
SELECT c1 FROM tab2 GROUP BY c1 having count(*)>1;
select c1,count(*) as con from x_tmp2 group by c1 minus select c1,count(*) as con from x_tmp1 group by c1
aaa
bbb
ccc
ddd表2的字段1中的记录如下:
aaa
bbb
ccc
ddd
ddd
ddd
ddd我想通过SQL,查出表2比表1多了3行ddd。
结果如下:
ddd
ddd
ddd
union
select c from t2 group by c havaing count(c)>1
union
select c from t1 minus select c from t2
union
select c from t2 group by c havaing count(c)>1
union
select c from t1 group by c havaing count(c)>1
from (select tb1.col as col1,tb2.col as col2,row_number()over(partition by tb2.col order by tb2.col)rn
from tb1,tb2
where tb1.col(+)=tb2.col)t
where t.col1 is null or (t.rn<>1);
union all
select id from B where id=(select id from B group by id having count(id)>1)
已测试,查询结果为:
eee
ddd
ddd
create table T1(name VARCHAR2(50));
create table T2(name VARCHAR2(50));
insert into T2 (NAME) values ('aaa');
insert into T2 (NAME) values ('bbb');
insert into T2 (NAME) values ('ddd');
insert into T2 (NAME) values ('ddd');
insert into T2 (NAME) values ('eee');
insert into T2 (NAME) values ('eee');
insert into T2 (NAME) values ('aaa');
insert into T2 (NAME) values ('bbb');
insert into T2 (NAME) values ('ccc');
insert into T2 (NAME) values ('aaa');insert into T1 (NAME)values ('aaa');
insert into T1 (NAME)values ('bbb');
insert into T1 (NAME)values ('ccc');
insert into T1 (NAME)values ('ddd');commit;select row_number() over(partition by name order by name) rn, t2.*
from t2
minus
select row_number() over(partition by name order by name) rn, t1.*
from t1;
select * from test2
group by c2
having count(*)>1
union
(
select * from test2
minus
select * from test1
)
union all
select id from B where id=(select id from B group by id having count(id)>1)