CREATE TABLE T_TYPE
(
T_TYPE_ID NUMBER,
T_TYPE_NAME VARCHAR2(20)
);
INSERT INTO T_TYPE
SELECT 1, '苹果' FROM DUAL UNION ALL
SELECT 2, '香蕉' FROM DUAL;CREATE TABLE T_INFO
(
T_PERSON_ID NUMBER,
T_PERSON_NAME VARCHAR(20),
T_TYPE_ID DECIMAL
);
INSERT INTO T_INFO
SELECT 1, '张三', 1 FROM DUAL UNION ALL
SELECT 1, '张三', 2 FROM DUAL UNION ALL
SELECT 2, '李四', 1 FROM DUAL
/*
求T_INFO表中T_PERSON_ID的T_TYPE_ID在表T_TYPE中都有的人
结果是张三
*/
(
T_TYPE_ID NUMBER,
T_TYPE_NAME VARCHAR2(20)
);
INSERT INTO T_TYPE
SELECT 1, '苹果' FROM DUAL UNION ALL
SELECT 2, '香蕉' FROM DUAL;CREATE TABLE T_INFO
(
T_PERSON_ID NUMBER,
T_PERSON_NAME VARCHAR(20),
T_TYPE_ID DECIMAL
);
INSERT INTO T_INFO
SELECT 1, '张三', 1 FROM DUAL UNION ALL
SELECT 1, '张三', 2 FROM DUAL UNION ALL
SELECT 2, '李四', 1 FROM DUAL
/*
求T_INFO表中T_PERSON_ID的T_TYPE_ID在表T_TYPE中都有的人
结果是张三
*/
WHERE A.T_PERSON_ID=B.T_TYPE_ID AND A.T_TYPE_ID=C.T_TYPE_ID
select T_PERSON_NAME
from t_info
group by T_PERSON_NAME
having count(*) = (select count(*) from t_type)
同样符合条件啊。
你的需求是不是找出T_TYPE中的ID都存在与INFO表中的记录?
SELECT DISTINCT T_Person_Name
FROM T_Info A
WHERE NOT EXISTS (
(SELECT T_Type_Id FROM T_Info WHERE T_Person_Name=A.T_Person_Name
MINUS
SELECT T_Type_Id FROM T_Type
)
UNION ALL
( SELECT T_Type_Id FROM T_Type
MINUS
SELECT T_Type_Id FROM T_Info WHERE T_Person_Name=A.T_Person_Name
) ) ;
如果不求这个限制,可以把union all前面的select语句去掉
from (select ti.t_person_id,
ti.t_person_name,
count(distinct tt.t_type_id) as ct
from t_info ti
inner join t_type tt
on ti.t_type_id = tt.t_type_id
group by ti.t_person_id, ti.t_person_name) tb
where tb.ct = (select count(*) from t_type)