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

2013-05-16 05:23:37 484 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

实战指南：将MediaPipe封装为Windows DLL，实现C++/C#高效调用在计算机视觉和多媒体处理领域，跨平台框架为开发者提供了强大的算法模型和计算能力。其核心原理在于通过预训练的机器学习模型和优化的计算图，实现对图像、视频流中特定目标（如人脸、手势、姿态）的实时检测与分析。这项技术的价值在于能够将复杂的AI能力模块化，降低应用开发门槛。在实际工程实践中，为了突破特定语言生态的限制并优化部署流程，开发者常需将核心功能封装成独立的库。例如，将Google的MediaPipe框架封装为Windows动态链接库（DLL），就是一个典型的工程实践。此举能有效解决语言壁垒和部署简化问题，使阅读详情

比如表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挑不出来。

...全文

Mac上SSH连接神器SecureCRT：从安装到高效使用的完整指南本文详细介绍了Mac上专业SSH连接工具SecureCRT的安装与高效使用技巧。从核心优势、会话管理到快捷键方案，全面解析如何提升远程开发与服务器管理效率。特别适合需要处理多服务器环境和复杂终端控制的Mac用户，SecureCRT的企业级加密和跨平台同步功能使其成为SSH连接的神器。阅读详情

charlesxu

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

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;

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

相关推荐