sql: 取出满足同时有a和b两个数据，且a发生的时间大于b的数据。

取出表中column x中同时有a和b，并且a发生时间大于b的数据。

举个例子：

SELECT * FROM STUDENT_COURSE
ORDER BY NAME ,COURSE 

 

希望取出同时选择了course1和course3，并且选course1的最小的changed_time比选course3 最小的changed_time小。

SELECT DISTINCT NAME
FROM 
(
SELECT C1.NAME NAME, C1_MIN,C3_MIN FROM
(
SELECT NAME, MIN(CHANGED_TIME) C1_MIN FROM  
(
SELECT * FROM STUDENT_COURSE 
WHERE 
NAME  IN (SELECT NAME  FROM STUDENT_COURSE WHERE COURSE = '1' GROUP BY name)
AND
NAME  IN (SELECT name  FROM STUDENT_COURSE sc WHERE sc.COURSE = '3' GROUP BY name)
) sc
WHERE COURSE = '1' 
GROUP BY NAME 
) C1
LEFT JOIN 
(
SELECT name, MIN(CHANGED_TIME) C3_MIN FROM  (
SELECT * FROM STUDENT_COURSE 
WHERE 
NAME  IN (SELECT NAME  FROM STUDENT_COURSE WHERE COURSE = '1' GROUP BY name)
AND
name  IN (SELECT name  FROM STUDENT_COURSE sc WHERE sc.COURSE = '3' GROUP BY name)
)
WHERE COURSE = '3'
GROUP BY NAME 
) C3
ON C1.NAME = C3.NAME
)
WHERE C1_MIN<C3_MIN

运行结果：

验证下：

可以看到

 

jack，tony，lily都是又选了course1，又选了course3的。 

但是jack和lily的course1 的最小changed_time小于course3的最小changed time。