php - 使用3 select将约束添加到上一个查询

我有这个查询：$stmt = $conn->prepare( 'SELECT * FROM table_shots WHERE idmatch in ( select * from ( SELECT mc_id FROM table_matches WHERE mc_hometeam = '. $idTeam .' OR mc_awayteam = '. $idTeam .' ORDER BY mc_date desc LIMIT '. $num .' ) temp_tab )' );现在，我需要向table_shots添加另一个条件。table_shots也具有以下字段：teamtype。该字段可以具有两个值：home_team或away_team。现在（以我的观点），我们需要找到一种从第三个SELECT知道$idTeam是在mc_hometeam还是mc_awayteam（来自table_matches）中的方法，以便添加新条件。新条件将按如下方式（通常）添加到表table_shots中：AND teamtype = home_team（如果$idTeam位于mc_hometeam中的table_matches中）或AND teamtype = away_team（如果$idTeam位于来自mc_awayteam）。建议这样做？我知道这很难，让我惊讶;） (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 为什么不使用简单联接而不是多选呢？$ stmt = $ conn-> prepare（'SELECT ts。*    FROM table_shots AS ts    加入table_matches AS tm ON ts.idmatch = tm.mc_id    在哪里（mc_hometeam ='。$ idTeam。'AND teamtype ='home_team'）或（mc_awayteam ='。$ idTeam。'AND teamtype ='away_team'）    订购mc_date desc    LIMIT'。 $ num）;另外，我建议使用PDO和参数，而不是将$ idTeam var连接到查询字符串。$ stmt = $ conn-> prepare（'SELECT ts。*    FROM table_shots AS ts    加入table_matches AS tm ON ts.idmatch = tm.mc_id    在哪里（mc_hometeam =：idTeam AND teamtype ='home_team'）或（mc_awayteam =：idTeam AND teamtype ='away_team'）    订购mc_date desc    LIMIT'。 $ num）;$ stmt-> execute（['：idTeam'=> $ idTeam]）;编辑：您是对的，LIMIT在错误的位置。乍一看，将两者结合起来是可行的。Edit2：下面的第一个查询有效，但是在第二个查询中，我将min（）放在错误的位置。第三个人也有同样的问题。修复它们。$ stmt = $ conn-> prepare（'SELECT ts。*    FROM table_shots AS ts    加入table_matches AS tm ON ts.idmatch = tm.mc_id    在哪里（（（mc_hometeam =：idTeam AND teamtype ='home_team'）OR（（mc_awayteam =：idTeam AND teamtype ='away_team'）））    AND idmatch in（                        选择 *                        来自（                            选择mc_id                            FROM table_matches                            哪里mc_hometeam =：idTeam                            或mc_awayteam =：idTeam                            订购mc_date desc                            LIMIT'。 $ num。                            ）temp_tab                    ））；$ stmt-> execute（['：idTeam'=> $ idTeam]）;还是这种方法也行得通，我不确定哪个更好。$ stmt = $ conn-> prepare（'SELECT ts。*    FROM table_shots AS ts    加入table_matches AS tm ON ts.idmatch = tm.mc_id    在哪里（（（mc_hometeam =：idTeam AND teamtype ='home_team'）OR（（mc_awayteam =：idTeam AND teamtype ='away_team'）））    AND mc_date> =（                        SELECT分钟（mc_date）                        来自（                            选择mc_date                            FROM table_matches                            哪里mc_hometeam =：idTeam                            或mc_awayteam =：idTeam                            订购mc_date desc                            LIMIT'。 $ num。                            ）temp_tab                    ））；$ stmt-> execute（['：idTeam'=> $ idTeam]）;或者，当然，您可以在单独的查询中获得minDate。我认为这更具可读性。$ stmt = $ conn-> prepare（'SELECT min（mc_date）                        来自（                            选择mc_date                            FROM table_matches                            哪里mc_hometeam =：idTeam                            或mc_awayteam =：idTeam                            订购mc_date desc                            LIMIT'。 $ num。                            ）temp_tab）;$ stmt-> execute（['：idTeam'=> $ idTeam]）;$ result = $ stmt-> fetch（\ PDO :: FETCH_ASSOC）;$ stmt = $ conn-> prepare（'SELECT ts。*    FROM table_shots AS ts    加入table_matches AS tm ON ts.idmatch = tm.mc_id    在哪里（（（mc_hometeam =：idTeam AND teamtype ='home_team'）OR（（mc_awayteam =：idTeam AND teamtype ='away_team'）））    AND mc_date> =：minDate'）;$ stmt-> execute（['：idTeam'=> $ idTeam，'：minDate'=> $ result ['minDate']]））; (adsbygoogle = window.adsbygoogle || []).push({});