mysql - 根据其他两列选择一列的 MAX 值，其中一列具有 MAX 值

Question

我有一个表数据如下，来自不同的表。仅当第三列具有值“债务”并且第一列(日期)具有最大值时，我才想从第四列中获取最大值。最终值基于 MAX(DATE) 而不是 MAX(PRICE)。所以用简单的语言来说，它是这样的

IF(MAX(DATE) && TYPE = 'debt'){
   MAX(PRICE);
}

这是可用数据的 ImageView 。

我当前使用的查询如下

SELECT
    MAX(CASE WHEN PAYMENTS.PAYMENT = 'debt' AND RECEIPTS.DATENEW THEN PAYMENTS.TOTAL END) AS TAKENDEBT,
    (CASE WHEN PAYMENTS.PAYMENT = 'debtpaid' THEN PAYMENTS.TOTAL END) AS GIVENDEBT,
    MAX(CASE WHEN PAYMENTS.PAYMENT = 'debt' THEN RECEIPTS.DATENEW END) AS TAKENDATE,
    MAX(CASE WHEN PAYMENTS.PAYMENT = 'debtpaid' THEN RECEIPTS.DATENEW END) AS GIVENDATE,
    (MAX(CASE WHEN PAYMENTS.PAYMENT = 'debt' THEN PAYMENTS.TOTAL END) + SUM((CASE WHEN PAYMENTS.PAYMENT = 'debtpaid' THEN PAYMENTS.TOTAL END))) AS DUE,        
    CASE WHEN PAYMENTS.PAYMENT = 'debt' OR PAYMENTS.PAYMENT = 'debtpaid' THEN CUSTOMERS.NAME END AS CUSTOMER 
FROM RECEIPTS
    INNER JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
    INNER JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
    INNER JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
WHERE
    (PAYMENTS.PAYMENT = 'debt'
    OR PAYMENTS.PAYMENT = 'debtpaid')
GROUP BY CUSTOMER
ORDER BY DUE DESC

这给了我如下所示的输出，带有红色圆圈，而我需要绿色圆圈中的值。这意味着它根据 MA​​X(PRICE) 获取值(value)，而我需要根据 MA​​X(DATE) 然后 MAX(PRICE) 获取值(value)，因为同一日期可以有多个值。

请不要让我将数据放入 Fiddle Thing 中，这是一个困惑的数据库，我设法进行了此查询，现在希望处于相同的结构中。谢谢

Answer 1

尝试类似的解决方案:

SELECT date, max(price) FROM yourTable 
  WHERE payment='debt' AND date = ( SELECT max(date) From yourTable)
  GROUP BY date

或

SELECT date ,max(price) FROM yourTable
  WHERE payment='debt'
  GROUP BY date
  ORDER BY date DESC
  LIMIT 1