mysql - 子查询给我不同的结果

Question

我有这个问题:

SELECT *
FROM gross_profit
WHERE client_name = 'john'
AND timestamp = 'lm153-1531439443738'

结果如下:

+--------+-------------+---------------------+-------------+------------------+------------------+
| id     | client_name | timestamp           | profit_loss | profit_loss_unit | profit_loss_type |
+--------+-------------+---------------------+-------------+------------------+------------------+
| 500392 | john        | lm153-1531439443738 |  1.90000000 | ADA              | profit           |
| 500393 | john        | lm153-1531439443738 |  0.00210900 | USDT             | fraction         |
| 500394 | john        | lm153-1531439443738 |  0.00000000 | BTC              | normal           |
| 509189 | john        | lm153-1531439443738 | -1.43713843 | ADA              | fee              |
+--------+-------------+---------------------+-------------+------------------+------------------+
4 rows in set (0.01 sec)

现在，我想消除所有具有 profit_loss_type = fee 的 timestamp。

所以我将查询修改为:

SELECT *
FROM gross_profit
WHERE client_name = 'john'
AND timestamp = 'lm153-1531439443738'
GROUP BY timestamp
HAVING SUM(CASE WHEN profit_loss_type = 'fee' THEN 1 ELSE 0 END) = 0

当然还有结果:空集(0.01 秒)

但是为什么，当我将该查询用作子查询时:

SELECT * FROM (
    SELECT timestamp
    FROM gross_profit
    WHERE client_name = 'john'
    AND profit_loss > 0
    GROUP BY timestamp
    HAVING SUM(CASE WHEN profit_loss_type = 'fee' THEN 1 ELSE 0 END) = 0
) AS t1
WHERE t1.timestamp = 'lm153-1531439443738'

它给了我:1 row in set (0.60 sec) 和 lm153-1531439443738 显示为结果。

这让我很困惑。为什么当我用作单个查询和子查询时结果不同？

Answer 1

我认为这是因为 WHERE 条件:

AND profit_loss > 0

在 HAVING 条件之前执行。

那么，前面的“fee”profit_loss类型被filtered_out了，你的结果就不再是空的了。

尝试删除此行。