一种使用join的方法

select t1.* from 
(
select * from results where EXAM ='ssc' and RESULT>4.0
    ) t1
  join
(
    select * from  results where EXAM ='hsc' and RESULT>4.2
) as t2 on t1.USER=t2.USER

您可以尝试在中使用条件聚合函数 HAVING 并检查两个条件是否成立。

CREATE TABLE T (
   ID INT,
   `USER` INT,
   EXAM VARCHAR(50),
   RESULT FLOAT
);

INSERT INTO T VALUES (1,10,'ssc', 4.2);
INSERT INTO T VALUES (2,10,'hsc', 4.5);
INSERT INTO T VALUES (3,11,'ssc', 4.0);
INSERT INTO T VALUES (4,11,'hsc', 4.4);
INSERT INTO T VALUES (5,12,'ssc', 5.0);
INSERT INTO T VALUES (6,12,'hsc', 5.0);
INSERT INTO T VALUES (7,13,'ssc', 3.0);
INSERT INTO T VALUES (8,13,'hsc', 3.0);

查询1：

SELECT USER  
FROM T 
GROUP BY USER 
HAVING 
    SUM(exam = 'ssc' and result > 4.0) = 1 
AND 
    SUM(exam = 'hsc' and result > 4.2) = 1

结果：

| USER |
|------|
|   10 |
|   12 |

使用条件聚合进行筛选

select user from tablename
group by user
having 
sum(case when exam='ssc' and result>4.0 then 2 when exam='hsc' and result>4.2 then 1 end)=3