mysql - 对于 WHERE 子句的两个单独结果，如何从单个表中的单个列获取最新的两个日期？

Question

我有一个返回 5 列的查询。

其中一列显示一行的最新日期值，其中一列包含字符串“seller-contact”或“buyer-contact”。

原始代码工作正常，看起来像这样。

SELECT
  TRIM(p.title) AS `Property Address`,
  TRIM(us.name) AS `Staff`,

  DATE_FORMAT(FROM_UNIXTIME(MAX( n.datecreated)), '%d-%m-%Y') AS `Last Contact Note`,

  DATEDIFF(
    DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())), '%Y-%m-%d'),
    DATE_FORMAT(FROM_UNIXTIME(MAX(n.datecreated)), '%Y-%m-%d')
  ) AS `Days Since Last Note`

FROM ias_property AS `p`
LEFT JOIN ias_note_stream AS `ns` ON ns.target_id = p.id
LEFT JOIN ias_note AS `n` ON ns.note_id = n.id
LEFT JOIN ias_user_staff AS `us` ON p.user_staff_id = us.id
WHERE p.status = 'sold' AND p.isarchived = 0
AND us.position = 'SP'
AND (n.data LIKE '%buyer-contact%' OR n.data LIKE '%seller-contact%')
GROUP BY p.id
ORDER BY MAX(n.datecreated) ASC

我尝试为每个结果使用两个子查询来实现上述目标。它不会失败或导致任何错误代码。但是在更大的 LIVE 数据库上，它花费的时间太长了(返回大约 270 行，我假设子查询的时间是以前的 270^270 倍；它永远不会完成它只是挂起) .

SELECT
TRIM(p.title) AS `Property Address`,
TRIM(us.name) AS `Staff`,

(SELECT DATE_FORMAT(FROM_UNIXTIME(MAX(n.datecreated)), '%d-%m-%Y') 
    FROM ias_property AS `p`
    LEFT JOIN ias_note_stream AS `ns` ON ns.target_id = p.id
    LEFT JOIN ias_note AS `n` ON ns.note_id = n.id
    WHERE n.data LIKE '%buyer-contact%'
    AND p.status = 'sold' AND p.isarchived = 0
    AND us.position = 'SP'
) AS `Last Buyer Contact Note`,

(SELECT DATE_FORMAT(FROM_UNIXTIME(MAX(n.datecreated)), '%d-%m-%Y') 
    FROM ias_property AS `p`
    LEFT JOIN ias_note_stream AS `ns` ON ns.target_id = p.id
    LEFT JOIN ias_note AS `n` ON ns.note_id = n.id
    WHERE n.data LIKE '%seller-contact%'
    AND p.status = 'sold' AND p.isarchived = 0
    AND us.position = 'SP'
) AS `Last Seller Contact Note`,

DATEDIFF(
    DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())), '%Y-%m-%d'),
    DATE_FORMAT(FROM_UNIXTIME(MAX(n.datecreated)), '%Y-%m-%d')
) AS `Days Since Last Note`

FROM ias_property AS `p`
LEFT JOIN ias_note_stream AS `ns` ON ns.target_id = p.id
LEFT JOIN ias_note AS `n` ON ns.note_id = n.id
LEFT JOIN ias_user_staff AS `us` ON p.user_staff_id = us.id
WHERE p.status = 'sold' AND p.isarchived = 0
AND us.position = 'SP'
AND (n.data LIKE '%buyer-contact%' OR n.data LIKE '%seller-contact%')
GROUP BY p.id
ORDER BY MAX(n.datecreated) ASC

有什么方法可以让事情变得更简单，这样我就可以拥有看起来像的东西

DATE_FORMAT(FROM_UNIXTIME(MAX( 
    n.datecreated WHERE n.data LIKE '%seller-%'
)), '%d-%m-%Y') AS `Last Buyer Contact Note;

TLDR:如何将来自同一列的 WHERE 子句的两个结果显示为两个单独的列(AS a、AS b)等？

Answer 1

您可以在 max 函数中放置一个 IF 以仅获取特定值的最大值。

DATE_FORMAT(FROM_UNIXTIME(MAX(IF(n.data LIKE '%buyer-contact%',n.datecreated,NULL))), '%d-%m-%Y') AS `Last Buyer Contact Note`
DATE_FORMAT(FROM_UNIXTIME(MAX(IF(n.data LIKE '%seller-contact%',n.datecreated,NULL))), '%d-%m-%Y') AS `Last Seller Contact Note`

添加这些应该不会影响原始查询的性能。