5,891
社区成员




CREATE TABLE DB2INST2.CAT_CMPCOLMN_A
(ID BIGINT NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, NO CYCLE, NO ORDER),
NAME VARCHAR(100),
MID INTEGER,
PID INTEGER,
WW INTEGER
)
DATA CAPTURE NONE;
CREATE TABLE DB2INST2.CAT_CMPCOLMN_B
(ID BIGINT NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, NO CYCLE, NO ORDER),
NAME VARCHAR(100),
MID INTEGER,
PID INTEGER,
EE INTEGER,
GG INTEGER
)
DATA CAPTURE NONE;
INSERT INTO DB2INST2.CAT_CMPCOLMN_A(ID,NAME,MID,PID,WW) VALUES
(1, 'AA', 1, 1, 3),
(2, 'BB', 2, 1, 4),
(3, 'CC', 2, 2, 4);
INSERT INTO DB2INST2.CAT_CMPCOLMN_B(ID,NAME,EE,MID,PID,GG) VALUES
(1, 'AA', 3, 1, 2, 3),
(2, 'CC', 2, 2, 2, 3),
(3, 'B' , 2, 2, 2, 2),
(4, 'BB', 2, 2, 1, 2);
--相等的
---方法一
SELECT * FROM CAT_CMPCOLMN_A A
WHERE EXISTS ( SELECT 1 FROM CAT_CMPCOLMN_B B WHERE NAME=A.NAME AND MID=A.MID AND PID=A.PID);
---方法二
SELECT * FROM CAT_CMPCOLMN_A A , CAT_CMPCOLMN_B B
WHERE A.MID=B.MID AND A.PID=B.PID AND A.NAME=B.NAME ;
--不等的
---方法一
SELECT * FROM CAT_CMPCOLMN_A A
WHERE NOT EXISTS (SELECT 1 FROM CAT_CMPCOLMN_B B WHERE NAME=A.NAME AND MID=A.MID AND PID=A.PID);
---方法二
SELECT * FROM CAT_CMPCOLMN_A WHERE ID NOT IN (
SELECT A.ID FROM CAT_CMPCOLMN_A A , CAT_CMPCOLMN_B B
WHERE A.MID=B.MID AND A.PID=B.PID AND A.NAME=B.NAME
);
select * from 表1
where not exists (
select 1 from 表2 where name=表1.name and mid=表1.mid and pid=表1.pid
)