怎么写个sql,可以把2个表的2个字段中不同和重复的值挑出来?

charlesxu 2013-05-16 05:23:37
比如表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挑不出来。
...全文
418 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
cyuyanzhiwen 2013-05-26
  • 打赏
  • 举报
回复
select id from B where id not in(select id from A) union all select id from B where id=(select id from B group by id having count(id)>1)
anan870622 2013-05-25
  • 打赏
  • 举报
回复

select * from test2
  group by c2
  having count(*)>1
union
(
  select * from test2
    minus
  select * from test1
)
rabitsky 2013-05-22
  • 打赏
  • 举报
回复
记得下次把create和insert语句贴出来
rabitsky 2013-05-22
  • 打赏
  • 举报
回复

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;
woqiang68 2013-05-21
  • 打赏
  • 举报
回复
select id from B where id not in(select id from A) union all select id from B where id=(select id from B group by id having count(id)>1) 已测试,查询结果为: eee ddd ddd
中国风 2013-05-17
  • 打赏
  • 举报
回复
加多一列记录数,如果只显示c1,嵌套一下就行了
select c1,count(*) as con from x_tmp2 group by c1 minus select c1,count(*) as con  from x_tmp1 group by c1
charlesxu 2013-05-17
  • 打赏
  • 举报
回复
ding
charlesxu 2013-05-17
  • 打赏
  • 举报
回复
楼上的方法能把重复1次的挑出来。但是如果表2里的数据比表1多好几行,也只显示1行重复的。 比如表1的字段1中的记录如下: aaa bbb ccc ddd 表2的字段1中的记录如下: aaa bbb ccc ddd ddd ddd ddd 我想通过SQL,查出表2比表1多了3行ddd。 结果如下: ddd ddd ddd
jascjasc 2013-05-17
  • 打赏
  • 举报
回复
select t.col2
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);
sych888 2013-05-17
  • 打赏
  • 举报
回复
select c from t2 minus select c from t1 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
zlc王丽 2013-05-17
  • 打赏
  • 举报
回复
select c from t2 minus select c from t1 union select c from t2 group by c havaing count(c)>1
wushangjimo 2013-05-16
  • 打赏
  • 举报
回复
SELECT c1 FROM tab2 WHERE not exisis(SELECT 1 FROM tab1 WHERE tab1.c1=tab2.c1) union SELECT c1 FROM tab2 GROUP BY c1 having count(*)>1;

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧