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

v1uwarro  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(382)

我有个问题:

$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 如果 $idTeammc_hometeam 或者 mc_awayteam (来自 table_matches )以添加新条件。
新条件将添加到表中 table_shots (技术上)像这样: AND teamtype = home_team (如果 $idTeammc_hometeamtable_matches )或者 AND teamtype = away_team (如果 $idTeammc_awayteamtable_matches ).
对此有何建议?
我知道这个很难,让我吃惊;)

qyuhtwio

qyuhtwio1#

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

$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']]);

相关问题