我正在使用postresql,但我在构建sql查询方面真的很糟糕。我有这个查询,它工作:
SELECT handhistories FROM handhistories
JOIN pokerhands using (pokerhand_id)
JOIN gametypes using (gametype_id)
RIGHT OUTER JOIN playerhandscashkeycolumns using (pokerhand_id)
WHERE pokerhands.site_id=0
AND pokerhands.numberofplayers>=5 and pokerhands.numberofplayers<=7
AND (bigblind = 2 OR bigblind = 4 )
AND player_id in
(SELECT player_id FROM playerhandscashkeycolumns GROUP BY player_id
HAVING AVG(case didvpip when true then 100::real else 0 end) <= 20 )
但是我还想限制最后一个"having"从底部开始,所以它将是这样的,但当然它不起作用。
SELECT handhistories FROM handhistories
JOIN pokerhands using (pokerhand_id)
JOIN gametypes using (gametype_id)
RIGHT OUTER JOIN playerhandscashkeycolumns using (pokerhand_id)
WHERE pokerhands.site_id=0
AND pokerhands.numberofplayers>=5 and pokerhands.numberofplayers<=7
AND (bigblind = 2 OR bigblind = 4 )
AND player_id in
(SELECT player_id FROM playerhandscashkeycolumns GROUP BY player_id
HAVING AVG(case didvpip when true then 100::real else 0 end) <= 20
AND HAVING AVG(case didvpip when true then 100::real else 0 end) > 10 )
如何"保存"值之后,所以我可以比较它也从底部?谢谢大家。
这主要是@wildplasser已经指出的
. .减去BETWEEN
的错误. .加上JOIN
代替IN
结构,这通常在PostgreSQL中更快。
. .更容易阅读
SELECT handhistories
FROM handhistories
JOIN pokerhands USING (pokerhand_id)
JOIN gametypes USING (gametype_id)
RIGHT JOIN playerhandscashkeycolumns USING (pokerhand_id)
JOIN (
SELECT player_id
FROM playerhandscashkeycolumns
GROUP BY player_id
HAVING avg(CASE WHEN didvpip THEN 100::real ELSE 0 END) > 10
AND avg(CASE WHEN didvpip THEN 100::real ELSE 0 END) <= 20
) p USING (player_id)
WHERE pokerhands.site_id = 0
AND pokerhands.numberofplayers BETWEEN 5 AND 7
AND bigblind IN (2,4);
您对某些列(如pokerhands.site_id
)进行了表限定,但对其他列(如handhistories
)不进行了表限定,您可能需要将其清除。
BETWEEN适合你吗?
HAVING AVG(case didvpip when true then 100::real else 0 end) BETWEEN 10 AND 20
(BTW:丑陋的SQL语法,重用AND关键字)
UPDATE:也可以用来简化查询的其余部分:
AND pokerhands.numberofplayers BETWEEN 5 AND 7
AND bigblind IN ( 2, 4 )