MySQL-向左连接添加子句

Question

我有一个名为属性 (p) 的表和另一个名为证书 (c) 的表。可以针对每个属性分配多个证书，或者根本不分配证书。我需要生成一个使用联接的查询，并且仅显示每个属性的证书表中的一个证书。显示的一张证书必须是最近到期日期的证书。证书表中有一个名为“certificate_expiry_date”的字段。简单的连接是 p.property_id = c.certificate_property，但这当前输出所有证书。

我的查询尝试

这是我到目前为止的查询；

SELECT DISTINCT t.tenancy_property, t.*, p.*, c.* FROM tenancy t
INNER JOIN property p
on t.tenancy_property = p.property_id
LEFT JOIN
(
    SELECT *
    FROM certificate
    WHERE certificate_expiry_date > CURDATE()
    ORDER BY certificate_expiry_date DESC
    LIMIT 1
) c ON p.property_id = c.certificate_property
WHERE t.tenancy_type='1' AND p.property_mains_gas_supply='1' AND p.property_availability='2' ORDER BY t.tenancy_id DESC LIMIT {$startpoint} , {$per_page}

此查询执行良好，但似乎没有考虑证书表上的左联接。

表证书的表结构

CREATE TABLE IF NOT EXISTS `certificate` (
  `certificate_id` int(11) NOT NULL AUTO_INCREMENT,
  `certificate_property` int(11) DEFAULT NULL,
  `certificate_type` tinyint(4) DEFAULT NULL,
  `certificate_reference` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `certificate_start_date` date DEFAULT NULL,
  `certificate_expiry_date` date DEFAULT NULL,
  `certificate_notes` text COLLATE utf8_bin,
  `certificate_renewal_instructed` tinyint(4) DEFAULT NULL,
  `certificate_renewal_contractor` int(11) DEFAULT NULL,
  PRIMARY KEY (`certificate_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=219 ;

Answer 1

如果我们只需要从certificates 表中返回一两列，有时可以在 SELECT 列表中使用相关子查询。

这种方法对大型表有一些性能影响；但对于某些用例，如果有适当的索引可用，这可能是一种可行的方法。

 SELECT p.id
      , p.somecol

      , ( SELECT c.col
            FROM certificate c
           WHERE c.property_id = p.id
           ORDER BY c.date_added DESC, c.id DESC
           LIMIT 1
        ) AS most_recent_cert_col

      , ( SELECT c.date_added
            FROM certificate c
           WHERE c.property_id = p.id
           ORDER BY c.date_added DESC, c.id DESC
           LIMIT 1
        ) AS most_recent_cert_date_added

   FROM property p 
  WHERE ... 
 ORDER BY ...