mysql - 在查询中选择导致相反结果的查询

Question

我在尝试显示选择查询中的选择中的正确列时遇到问题

SELECT
      merchant.merchantname  'MerchantName'
    , COALESCE(COUNT(transaction.transactionid),0) 'NoofTransaction'
    , COALESCE(SUM(transaction.transactionamount),0) 'TotalAmount'
    , (         SELECT
                  statement.statementbalance
            FROM statement
            WHERE transaction.transactionid = statement.transactionid
            ORDER BY
                  statementbalance DESC LIMIT 1 
      )                      
      AS 'BalanceRemaining'
FROM merchant
LEFT JOIN transaction ON merchant.merchantid = transaction.merchantid
AND transaction.transactiondate = '2018-01-16'
GROUP BY
      merchant.merchantid
ORDER BY
      merchant.merchantid ASC;

这是按 transactionid 排序的语句余额。在日期 '2018-01-16' 的 BalanceRemaining 列中，我应该得到 7。相反，当 sql 查询专门按降序排序并限制row 返回到 1。如果我将其更改为从 transactionid 获取，也会发生同样的情况

    statementbalance
    ===
    500
    .
    .
    233
    90
    7

表的示例数据:http://sqlfiddle.com/#!9/0ec29f

Answer 1

您没有按正确的列排序。如果您想要最新日期的值，那么您需要按 DATE 和 TIME 列降序排序:

SELECT m.merchantname as MerchantName,         COALESCE(COUNT(t.transactionid), 0) as NoofTransaction,        COALESCE(SUM(t.transactionamount), 0) as TotalAmount,       (SELECT s.statementbalance        FROM statement s        WHERE m.merchantid = s.merchantid        AND s.statementdate <= '2018-01-16'        ORDER BY s.statementdate DESC, s.statementtime DESC        LIMIT 1        ) as BalanceRemainingFROM merchant m LEFT JOIN transaction t ON m.merchantid = t.merchantid                       AND t.transactiondate = '2018-01-16'GROUP BY m.merchantidORDER BY m.merchantid ASC;

To enable this also change the way the data is correlated, NOT by transactionid, but by merchantid instead.

| MerchantName | NoofTransaction | TotalAmount | BalanceRemaining |
|--------------|-----------------|-------------|------------------|
|         Dog1 |               0 |           0 |           (null) |
|         Cat2 |              13 |         115 |            24.16 |
|      Parrot3 |               1 |          20 |           299.25 |
|      Beaver4 |               0 |           0 |           (null) |

参见:http://sqlfiddle.com/#!9/cc2440/3