blocks|key|2765099|text|尝试一下，因为它对我来说很好，我也遇到过类似的情况，我的表名是event和area_interest。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2765100|+$query+=+new+Query();
+$subquery+=+new+Query();
+$subquery+->select(['area_intrest.interest'])
++++++++++++->from('area_intrest'+)
++++++++++++->where('area_intrest.id+=+event.interest_id');

+$query+->select(['event.title',$query3])
++++++++++++->from('event');
+$command+=+$query->createCommand();
+$data+=+$command->queryAll();|code-block|syntax|javascript|2765101|在您的条件下，您甚至可以在引号中的子查询中使用max，它将很好地工作。|2765102|entityMap^0|V|5|11|D|0|0|N|3|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

Try this because it works fine for me, i had similar situation where my table names are <code>event</code> and <code>area_interest</code>

<pre><code> $query = new Query();
 $subquery = new Query();
 $subquery -&gt;select(['area_intrest.interest'])
 -&gt;from('area_intrest' )
 -&gt;where('area_intrest.id = event.interest_id');

 $query -&gt;select(['event.title',$query3])
 -&gt;from('event');
 $command = $query-&gt;createCommand();
 $data = $command-&gt;queryAll();
</code></pre>

In your condition you can even use <code>max</code> in the subquery in quotes and it will work fine.

blocks|key|2299495|text|从SQL的角度来看，示例查询是在select子句中使用“关联子查询”，这通常是形成查询的一种效率很低的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2299496|SELECT+ParentTable.*,+(SELECT+MAX(ChildTable.NumberField)+
+++++++++++++++++++++++FROM+ChildTable
+++++++++++++++++++++++WHERE+ChildTable.FK_Id+=+ParentTable.Id)
FROM+ParentTable|code-block|syntax|javascript|2299497|虽然乍一看，它可能更复杂，因此效率较低，但通常最好避免select子句中的“相关子查询”，而使用“派生表”代替，如下所示：|2299498|SELECT+ParentTable.*,c.MxNum
FROM+ParentTable
LEFT+JOIN+(
+++++++++++SELECT+ChildTable.FK_Id,+MAX(ChildTable.NumberField)+as+MxNum+FROM+ChildTable
+++++++++++GROUP+BY+ChildTable.FK_Id
+++++++++++)+AS+c+ON+c.FK_Id+=+ParentTable.Id|2299499|注意，带有select子句的关联子查询可能返回NULL，因此，如果用派生表替换它们，等效的联接类型是左外部联接(或简单地说是左联接)，因为这也允许空结果。但是，如果列不需要NULL，则使用更有效的内部联接。|2299500|事先为不知道Yii2语法表示歉意，但似乎有必要知道一种有效的替代方法，这可能有助于解决问题。|2299501|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

The example query, from a SQL perspective is using a "correlated subquery" inside the select clause and often this is a very inefficient way of forming a query.

<pre><code>SELECT ParentTable.*, (SELECT MAX(ChildTable.NumberField) 
 FROM ChildTable
 WHERE ChildTable.FK_Id = ParentTable.Id)
FROM ParentTable
</code></pre>

Although it may appear at first glance to be more complex and hence less efficient, it is generally better for performance to avoid "correlated subqueries" in a select clause and substitute using a "derived table" instead, like this:

<pre><code>SELECT ParentTable.*,c.MxNum
FROM ParentTable
LEFT JOIN (
 SELECT ChildTable.FK_Id, MAX(ChildTable.NumberField) as MxNum FROM ChildTable
 GROUP BY ChildTable.FK_Id
 ) AS c ON c.FK_Id = ParentTable.Id
</code></pre>

Note, a correlated subquery with a select clause might return NULL, and due to this, if replacing them with a derived table the equivalent join type is a LEFT OUTER JOIN (or simply LEFT JOIN) as this also permits a NULL result. However if you don't need NULLs for the column, then use the more efficient INNER JOIN instead.

Apologies in advance for not knowing Yii2 syntax, but it seems relevant to know an effective alternative approach which may assist in solving the issue.

blocks|key|2765243|text|谢谢Used_By_Already和Mike，您的答复帮助我完成了下面的Yii2+2/MySQL最终解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2765244|$query+=+ParentClass::find();
$subQuery+=+ChildClass::find()->select('fk_id,+max(column1)+as+column1')->groupBy('fk_id');
$query->leftJoin(['T'+=>+$subQuery],+'T.fk_id+=+parentTable.id');|code-block|syntax|javascript|2765245|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Thanks Used_By_Already and Mike Ross, your replies helped me arrive at the final complete Yii2/MySQL solution below.

<pre><code>$query = ParentClass::find();
$subQuery = ChildClass::find()-&gt;select('fk_id, max(column1) as column1')-&gt;groupBy('fk_id');
$query-&gt;leftJoin(['T' =&gt; $subQuery], 'T.fk_id = parentTable.id');
</code></pre>

blocks|key|2765262|text|试试这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2765263|$submodel+=+ChildClass::find()
++++++++++->select([+'ChildTable.fk_id',+'MAX(ChildTable.NumberField)',+])
++++++++++->where("...")
++++++++++->asArray();

$model++++=+ParentClass::find()
++++++++++->select([+'parentTable.*',+'b.*',+])
++++++++++->innerJoin(['b'+=>+$submodel],+'b.`fk_id`+=+parentTable.`id`')
++++++++++->all();|code-block|syntax|javascript|2765264|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this one

<pre><code>$submodel = ChildClass::find()
 -&gt;select([ 'ChildTable.fk_id', 'MAX(ChildTable.NumberField)', ])
 -&gt;where("...")
 -&gt;asArray();

$model = ParentClass::find()
 -&gt;select([ 'parentTable.*', 'b.*', ])
 -&gt;innerJoin(['b' =&gt; $submodel], 'b.`fk_id` = parentTable.`id`')
 -&gt;all();
</code></pre>

I was able to find simple examples of sub-query building, but am unable to figure out nor find the solution when I need to include a WHERE condition. I am trying to simulate the following statement...

<pre><code>SELECT ParentTable.*, (SELECT MAX(ChildTable.NumberField) 
 FROM ChildTable
 WHERE ChildTable.FK_Id = ParentTable.Id)
FROM ParentTable
</code></pre>

Guess I'd need something like...

<pre><code>$query = ParentClass::find()
 -&gt;addSelect(
 ChildClass::find()
 -&gt;where('childTable.fk_id=parentTable.id')
 -&gt;max('childTable.field1')
 );
</code></pre>

But it gives me an error: Column not found: 1054 Unknown column 'parentTable.id' in 'where clause'

EDIT:
Including actual class/table names...

<pre><code>$endQuery = UnitSchedule::find()
 -&gt;where('cm_courseschedule.id=cm_unitschedule.csch_id')
 -&gt;max('cm_unitschedule.slot');
$query = CourseSchedule::find();
$query-&gt;addSelect($endQuery);
</code></pre>

SELECT sub-query with WHERE condition in Yii2 find() / QueryBuilder

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我能够找到子查询构建的简单示例，但是当我需要包含WHERE条件时，我无法找到解决方案。我试着模拟以下陈述..。SELECT ParentTable.*, (SELECT MAX(ChildTable.NumberField)                        FROM ChildTable         ...

问在Yii2 find() / QueryBuilder中选择具有WHERE条件的子查询
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Yii2 find() / QueryBuilder中选择具有WHERE条件的子查询EN