我有下表和一个SQL查询,以确定表中是否有任何项目的买价高于卖价。
询问:
SELECT id, name
FROM orders
GROUP BY id, name
HAVING MAX(CASE WHEN buy = 'false' THEN price END) > MIN(CASE WHEN buy = 'true' THEN price END)
问题在于,这仅返回两行:
我正在寻找的结果应该在所有列中显示所有买价高于卖价的物品。继续阅读,GROUP BY
我发现它只返回不同的值。我是否必须使用联接才能达到最终结果?
|id |buy |date |price |name
--------------------------------------------------
3 false 2017-01-04 19:23:12.000 7 bread
3 false 2017-01-04 19:23:12.000 4 bread
2 false 2017-01-04 19:23:12.000 7 grapes
使用此查询,结果是不应以红色突出显示的行下方的表存在。
SELECT *
FROM orders
WHERE id IN (SELECT id
FROM orders
GROUP BY id, name
HAVING MAX(CASE WHEN buy = 'false' THEN price END) > MIN(CASE WHEN buy = 'true' THEN price END))
将现有查询用作子查询:
SELECT * from orders where id in
(
SELECT id
FROM orders
GROUP BY id, name
HAVING MAX(CASE WHEN buy = 'false' THEN price END) > MIN(CASE WHEN buy = 'true' THEN price END)
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句