MySQL 使用 WHERE 语句定位特定行 [英] MySQL Use WHERE statement to target particular row

问题描述

我正在尝试查询一个专注于 3 个不同列的表.我有 nid、vid 和 title 列.我必须遍历每个 nid 值，获取每个 nid 值的最高 vid，一旦我获得最高的 vid，我就必须获得 vid 同一行中的标题.最终，我需要每个标题的列表.我有第一个两个步骤.我唯一需要帮助的部分是获取正确的标题内容，因为它再次必须是与最高视频位于同一行的标题.我认为 where 语句是一个很好的方法，但我被卡住了.附上表格截图.

I'm trying to query a table that focused on 3 different columns. I have columns nid, vid and title. I have to go through each nid value, grab the highest vid of each nid value, once I get the highest vid I then have to get the title that's in the same row of the vid. Ultimately I'll need a list of each title. I have the 1st two steps down. The only part I need help with is grabbing the correct title content because once again it has to be the title that's in the same row as the highest vid. I thought a where statement would be a good way to do it but I'm stuck. Attached is a screenshot of the table.

$queryNodeRevision = "SELECT nid, MAX(vid) as vid, title FROM node_revision WHERE title = vid GROUP BY nid";
    // line above creates variable $queryNodeRevision > 

    $results = mysqli_query($connection, $queryNodeRevision) or die("Bad Query: $results");
    // line above creates variable $results > actually queries that database and passes in variable "$queryNodeRevision"

    while ($row = mysqli_fetch_array($results)) {
      // line above creates while loop that loops through > $row = mysqli_fetch_array($results)
      // $row is variable that's set to mysqli_fetch_array (with variable $results being passed in)
      // mysqli_fetch_array > creates an associate array for each row in a table
      // $results > variable that's being passed into associative array that represents the variable that's querying "SELECT nid FROM node_revision";
      $currentNID = $row['nid'];
      // line above creates variable that represents the current 'nid' of row (aka the key)
      // $row['nid'] = gets the key # of the current 'nid'
      $currentVID = $row['vid'];
      // line above creates variable that represents the current value of the 'vid' (the number you want to compare)
      // $row['vid'] = gets the value of the current 'vid'  
      $theTitleIWant = $row['title'];
      // line above creates variable that represents the current value of the 'title'
      // $row['title'] = gets the value of the current 'title'  
      echo "<h1>" . $row['title'] . "</h1>";
      // line prints out desired 'title' into h1 tag
    } // line closes while loop

下面是我试图用 where 语句完成代码的那一行.

Below is the line with the where statement I'm trying to accomplish my code with.

$queryNodeRevision = "SELECT nid, MAX(vid) as vid, title FROM node_revision WHERE title = vid GROUP BY nid";[![enter image description here][1]][1]

推荐答案

考虑运行聚合查询(忽略对 title 的任何引用)，该查询连接回单元级表.下面返回对应于每个 nid 的最高 vid 的 title.

Consider running your aggregate query (leaving out any reference to title) that joins back to the unit level table. Below returns the title corresponding to highest vid for each nid.

SELECT n.nid, n.vid, n.title
FROM node_revision n
INNER JOIN
   (SELECT nid, MAX(vid) as max_vid 
    FROM node_revision 
    GROUP BY nid
   ) AS agg
ON agg.nid = n.nid AND agg.max_vid = n.vid

这篇关于MySQL 使用 WHERE 语句定位特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！