mysql - 我如何在此查询中使用子查询的结果？

Question

示例，我有 SQL - 查询:

SELECT
    (SELECT value->>"$[0].value" 
        FROM leads_fields 
        WHERE leads_fields.field_id = 244991 AND leads_fields.lead_id = leads.external_id)
        as 'coefRL',
    (SELECT value->>"$[0].value" 
        FROM leads_fields 
        WHERE leads_fields.field_id = 244943 AND leads_fields.lead_id = leads.external_id)
        as 'coefWF'

FROM leads

为了获得这些值的差异，我需要像这样使用第三个子查询复制这些请求？

(
    (SELECT value->>"$[0].value" 
     FROM leads_fields 
     WHERE leads_fields.field_id = 244991 AND leads_fields.lead_id = leads.external_id)
        -
     (SELECT value->>"$[0].value" 
      FROM leads_fields 
      WHERE leads_fields.field_id = 244943 AND leads_fields.lead_id = leads.external_id)
) as 'difference'

或者有更实用的方法，例如(coefRL - coefWF)？

Answer 1

这是一种无需第三个子查询即可实现的方法...

SELECT coefRL,
  coefWF,
  coefRL-coefWF difference
FROM
  (SELECT
    (SELECT value->>"$[0].value"
    FROM leads_fields
    WHERE leads_fields.field_id = 244991
    AND leads_fields.lead_id    = leads.external_id
    ) AS 'coefRL',
    (SELECT value->>"$[0].value"
    FROM leads_fields
    WHERE leads_fields.field_id = 244943
    AND leads_fields.lead_id    = leads.external_id
    ) AS 'coefWF'
  FROM leads
  )