mysql - MySQL SELECT 行如果不存在或如果存在做条件

Question

我在处理这个查询时遇到了麻烦。我想选择一系列学生，他们必须检查一些条件。

我的第一个查询是：

SELECT a.id_card, a.cf_id, a.name, e.cf_created, a.email, e.cf_id AS id_exam
            FROM examns_table AS e, students_table AS a 
            WHERE 
                e.paid != 'NULL'  
                AND e.cf_created < $startDate 
                AND e.id_student = a.cf_id 
            ORDER BY e.cf_created DESC
            LIMIT 100 , 10

这个查询很完美。另一方面，我想检查收到记录电子邮件的学生。做到这一点的完美查询是：

SELECT * FROM email_sending_table as b, students_table as a where a.cf_id=b.id_student and b.sent_date is not null and b.sent_date < '2020-09-15 12:46:46'

此时，我需要链接两个查询，将第二个查询作为条件查询。我的意思是，如果在 email_sending_table 中存在一些具有 cf_id 的学生，则抛出第二个查询，检查日期是否不为空以及该日期是否小于“2020-09-15 12:46:46”。

我得到了这个：

SELECT b.sent_date, a.id_card, a.cf_id, a.name, e.cf_created, a.email, e.cf_id AS id_exam FROM exam_table AS e, students_table AS a, email_sending_table as b WHERE e.cf_created < '2020-09-15 12:46:46' and e.paid != 'NULL' AND e.id_student = a.cf_id and b.sent_date is not null and b.sent_date < '2020-09-15 12:46:46'

但是通过这个查询，我得到了与这个条件的所有巧合。我真的很想做这样的事情：

SELECT b.sent_date, a.id_card, a.cf_id, a.name, e.cf_created, a.email, e.cf_id AS id_exam FROM exam_table AS e, students_table AS a, email_sending_table as b WHERE e.cf_created < '2020-09-15 12:46:46' and e.paid != 'NULL' AND e.id_student = a.cf_id and (IF EXISTS b.id_card = a.id_card (b.sent_date is not null and b.sent_date < '2020-09-15 12:46:46'))

我试图通过这样的 UNION 方式来做：

SELECT a.id_card, a.cf_id, a.name, e.cf_created, a.email, e.cf_id AS id_exam
            FROM examns_table AS e, students_table AS a 
            WHERE 
                e.paid != 'NULL'  
                AND e.cf_created < $startDate 
                AND e.id_student = a.cf_id 
            ORDER BY e.cf_created DESC UNION SELECT * FROM email_sending_table as b, students_table as a where a.cf_id=b.id_student and b.sent_date is not null and b.sent_date < '2020-09-15 12:46:46'

我也尝试使用 LEFT JOIN，例如：

SELECT a.id_card, a.cf_id, a.name, e.cf_created, a.email, e.cf_id AS id_examenFROM examns_table AS e, students_table AS a INNER JOIN email_sendint_table as b ON (a.cf_id=b.id_student) WHERE e.paid != 'NULL' AND e.cf_created < '2017-09-15 12:46:46' AND e.id_student = a.cf_id AND (b.sent_date IS NOT NULL AND b.sent_date < '2020-09-15 12:46:46' ) ORDER BY e.cf_created DESC LIMIT 100 , 10 

我是 MySQL 的初学者，所以欢迎任何建议。谢谢！

标签： mysqlsql