mysql - mysql中如何使用没有join的表

Question

我有多个彼此相关的表，但我有一个表与此表中的任何一个都不相关，并且希望在(where 子句)中使用此表。那么我该怎么做呢？

 /*  this is the table i want to use without join (d_checkupnext_alert.chx_days)
  *  notice : chx_days return on value with one row only
  */

SELECT 
  d_checkupinfo.*,
  d_branch.*,
  d_checkup.*,
  d_patient.*,
  d_checkupnext_alert.chx_days
FROM d_checkupinfo
  LEFT JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  LEFT JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  LEFT JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
   WHERE CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL chx_days day) AND FROM_UNIXTIME(chi_nextdate)

/*更新*/我使用这段代码，它可以工作，但给我错误

 SET @days = (select chx_days from d_checkupnext_alert LIMIT 1);
 SELECT d_checkupinfo.*, d_branch.*, d_checkup.*,
d_patient.* 
FROM d_checkupinfo
  LEFT JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  LEFT JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  LEFT JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id 
WHERE CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL @days day) AND FROM_UNIXTIME(chi_nextdate)

Answer 1

最好的方法是使用简单的 JOIN 或“,”，如下所示:

SELECT 
  d_checkupinfo.*,
  d_branch.*,
  d_checkup.*,
  d_patient.*,
  d_checkupnext_alert.chx_days
FROM d_checkupinfo
  LEFT JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  LEFT JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  LEFT JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
  , d_checkupnext_alert
   WHERE CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL chx_days day) AND FROM_UNIXTIME(chi_nextdate)

但是如果您不想，您可以使用系统变量:请参阅 "MySQL System Variables"