取出表中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。