MySQL 使用 Zend DB 编写子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1340564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing a subquery using Zend DB
提问by PJE
I am having some problems turning the SQL below into a Zend Db query.
我在将下面的 SQL 转换为 Zend Db 查询时遇到了一些问题。
$select = ' SELECT s.id, i.id as instance_id, i.reference, i.name, i.sic_code, i.start_date
FROM sles s
JOIN sle_instances i
ON s.id = i.sle_id
WHERE i.id = ( SELECT MAX(id)
FROM sle_instances
WHERE sle_id = s.id
)
ORDER BY i.name ASC';
I have got as far as the code before - but Zend Db isn't producing the query correctly. Can any one show me what I missing??
我已经了解了之前的代码 - 但 Zend Db 没有正确生成查询。谁能告诉我我错过了什么？
$select = $db->select() ->from('sles', array( 'id',
'instance_id' => 'sle_instances.id',
'reference' => 'sle_instances.reference',
'name' => 'sle_instances.name',
'sic_code' => 'sle_instances.sic_code',
'start_date' => 'sle_instances.start_date'
)
)
->join('sle_instances', 'sles.id = sle_instances.sle_id')
->where('sles.id = (SELECT MAX(id) FROM sle_instances WHERE sle_id = sles.id)')
->order('sle_instances.name ASC');
The SQL does work by the way. I am rewriting it using Zend Db as I wish to use the Zend Paginator functionality.
顺便说一下，SQL 确实有效。我正在使用 Zend Db 重写它，因为我希望使用 Zend Paginator 功能。
Any help is greatly appreciated.
任何帮助是极大的赞赏。
PJ
PJ
采纳答案by karim79
This:
这个：
$select = $db->select()->from(array("s" => "sles"), array("s.id","i.id as instanceid","i.reference","i.name","i.sic_code","i.start_date"))
->join(array('i' => "sle_instances"),"s.id = i.sle_id",array())
->where("i.id = (select max(id) from sle_instances where sle_id = s.id)")
->order('i.name asc');
Gives this:
给出了这个：
"SELECT `s`.`id`, `i`.`id` AS `instanceid`, `i`.`reference`, `i`.`name`, `i`.`sic_code`, `i`.`start_date` FROM `sles` AS `s`
INNER JOIN `sle_instances` AS `i` ON s.id = i.sle_id WHERE (i.id = (select max(id) from sle_instances where sle_id = s.id)) ORDER BY `i`.`name` asc"
回答by Justin
if you want, you can take what @karim79 did and turn your subselect into a $this->select() as well...
如果你愿意，你可以采用@karim79 所做的并将你的子选择转换为 $this->select() ......
$subselect = $db->select()
->from('sle_instances', array(new Zend_Db_Expr('max(id)')))
->where('sle_id = s.id');
$select = $db->select()->from(array("s" => "sles"),
array("s.id","i.id as instanceid","i.reference","i.name","i.sic_code","i.start_date"))
->join(array('i' => "sle_instances"),"s.id = i.sle_id",array())
->where("i.id = ($subselect)")
->order('i.name asc');
print($select);
//SELECT `s`.`id`, `i`.`id` AS `instanceid`, `i`.`reference`, `i`.`name`, `i`.`sic_code`, `i`.`start_date` FROM `sles` AS `s` INNER JOIN `sle_instances` AS `i` ON s.id = i.sle_id WHERE (i.id = (SELECT max(id) FROM `sle_instances` WHERE (sle_id = s.id))) ORDER BY `i`.`name` asc
回答by Ronaldo Faria Lima
I had a very similar problem and I found that this query can be easily written as follows:
我有一个非常相似的问题，我发现这个查询可以很容易地写成如下：
$select = $db->select()
->from (
array("s" => "sles"),
array(
"s.id",
"instanceid" => "i.id",
"i.reference",
"i.name",
"i.sic_code",
"i.start_date")
)
->join(
array('i' => "sle_instances"),
"s.id = i.sle_id",
array()
)
->where ("i.id = (" .
$db->select()
->from('sle_instances', array(new Zend_Db_Expr('max(id)')))
->where('sle_id = s.id');
.")")
->order('i.name asc');
print($select);
It is exactly the same as people has already stated here. But I felt it is a little easier to read since the sub-query dependencies are more evident.
这与人们在这里已经说过的完全相同。但是我觉得它更容易阅读，因为子查询依赖关系更加明显。
回答by Ascherer
Great question! Thank you for this one. Also wanted to throw out that if you are trying to do a group after an order, you can also use this syntax by something very similar to the following
好问题！谢谢你的这个。还想抛出，如果您尝试在订单后进行分组，您也可以通过类似于以下内容的方式使用此语法
$subquery = $this->_datawarehouse->select()
->from('revenueLog')
->where('Date '.$ReturnDate)
->order('Date DESC');
$this->view->end = microtime();
$format = new Zend_Db_Expr('DATE_FORMAT(`Date`,"%d-%m-%y")');
$select = $this->_datawarehouse->select()
->from(array('subquery'=>$subquery))
->group('Client')
->group($format)
->order('Vertical ASC')
->order('Revenue DESC');
echo $select->__ToString();
$stmt = $this->_datawarehouse->query($select);
$data = $stmt->fetchAll();
for anyone wondering $ReturnDate is a string based on a user input, which usually ends up being a "BETWEEN 'date1' AND 'date2'"
对于任何想知道 $ReturnDate 是基于用户输入的字符串的人来说，它通常最终是“BETWEEN 'date1' AND 'date2'”
