使用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 .
表1也有此字段： teamtype . 此字段可以有两个值： home_team 或者 away_team .
现在（在我看来）我们需要的是找到一种从第三个Angular 去了解的方法 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 从 table_matches ).
对此有何建议？
我知道这个很难，让我吃惊；）

为什么不使用简单的连接而不是多个选择？

$stmt = $conn->prepare('SELECT ts.* 
    FROM table_shots AS ts 
    JOIN table_matches AS tm ON ts.idmatch = tm.mc_id
    WHERE (mc_hometeam = '. $idTeam .' AND teamtype = 'home_team') OR (mc_awayteam = '. $idTeam .' AND teamtype = 'away_team')
    ORDER BY mc_date desc 
    LIMIT '. $num);

另外，我建议使用pdo和参数，而不是将$idteam var连接到查询字符串。

$stmt = $conn->prepare('SELECT ts.* 
    FROM table_shots AS ts 
    JOIN table_matches AS tm ON ts.idmatch = tm.mc_id
    WHERE (mc_hometeam = :idTeam AND teamtype = 'home_team') OR (mc_awayteam = :idTeam AND teamtype = 'away_team')
    ORDER BY mc_date desc 
    LIMIT '. $num);
$stmt->execute([':idTeam' => $idTeam]);

编辑：你说得对，限制在错误的地方。乍一看，将两者结合起来应该是可行的。
edit2：下面的第一个查询有效，但在第二个查询中，我将min（）放错了位置。第三个也有同样的问题。修好了。

$stmt = $conn->prepare('SELECT ts.* 
    FROM table_shots AS ts 
    JOIN table_matches AS tm ON ts.idmatch = tm.mc_id
    WHERE ((mc_hometeam = :idTeam AND teamtype = 'home_team') OR (mc_awayteam = :idTeam AND teamtype = 'away_team'))
    AND 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
                    ));
$stmt->execute([':idTeam' => $idTeam]);

或者这种方法也能奏效，我不确定哪种更好。

$stmt = $conn->prepare('SELECT ts.* 
    FROM table_shots AS ts 
    JOIN table_matches AS tm ON ts.idmatch = tm.mc_id
    WHERE ((mc_hometeam = :idTeam AND teamtype = 'home_team') OR (mc_awayteam = :idTeam AND teamtype = 'away_team'))
    AND mc_date >= (
                        SELECT min(mc_date)
                        FROM (
                            SELECT mc_date
                            FROM table_matches 
                            WHERE mc_hometeam = :idTeam 
                            OR mc_awayteam = :idTeam 
                            ORDER BY mc_date desc 
                            LIMIT '. $num .'
                            ) temp_tab
                    ));
$stmt->execute([':idTeam' => $idTeam]);

当然，你也可以在一个单独的查询中找到mindate。我认为这更具可读性。

$stmt = $conn->prepare('SELECT min(mc_date)
                        FROM (
                            SELECT mc_date
                            FROM table_matches 
                            WHERE mc_hometeam = :idTeam 
                            OR mc_awayteam = :idTeam 
                            ORDER BY 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 
    JOIN table_matches AS tm ON ts.idmatch = tm.mc_id
    WHERE ((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']]);

使用3 select向上一个查询添加约束

1条答案

相关问题

热门标签

最新问答