gpt4 book ai didi

mysql - 使用表达式查询选择大于

转载 作者:行者123 更新时间:2023-11-29 07:50:43 25 4
gpt4 key购买 nike

我正在尝试编写以下查询:

SELECT * FROM program
WHERE (SELECT MAX(surface) FROM lot
WHERE lot.id_program=program.id) > $min_surface;

以下代码有效:

$min_surface=40;
$select->where('(SELECT MAX(surface) FROM lot WHERE lot.id_program=program.id) > '.$min_surface);

但是,我想使用 GreaterThan 谓词:

$select->where->greaterThan(
'(SELECT MAX(surface) FROM lot WHERE lot.id_program=program.id)'
,$min_surface
);

抛出以下错误:

Statement could not be executed (42000 - 1064 - You have an error in your SQL syntax;   
check the manual that corresponds to your MySQL server version
for the right syntax to use near
'`SELECT` `MAX``(``surface``)` `FROM` `lot` `WHERE` `lot`.`id_program``=``program' at line 1)

最佳答案

在您的情况下确实不需要使用子查询。您只需加入表即可。

SELECT program.*, MAX(surface) as max_surface 
FROM program
JOIN lot ON lot.id_program = program.id
GROUP BY program.id
HAVING max_surface > $min_surface

这个查询可以写

$select->from('program')
->join(
'lot',
'lot.id_program = program.id',
array('max_surface' => new Zend_Db_Expr('MAX(surface)'))
)
->group('program.id')
->having('max_surface > ?', $min_surface)

希望对你有帮助

关于mysql - 使用表达式查询选择大于,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26481458/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com