显示查询中的 11 个连续行，其中中间行包含一个变量 - Displaying 11 consecutive rows from a query where middle row contains a variable

在我的网页上，有一个名为$submission的变量。我想从下面的查询中显示正好 11 行：$submission等于 $row["title"] 的行，上面 5 行，下面 5 行。全部按points降序排列。

我该怎么做？

$sqlStr = "SELECT title, points, submissionid
             FROM submission 
         ORDER BY points DESC"; 

$result = mysql_query($sqlStr);
$arr = array();
$count=1;
echo "<table class='"samplesrec'">";
while ($row = mysql_fetch_array($result)) { 

    echo '<tr >';
    echo '<td>'.$count++.'.</td>';
    echo '<td class="sitename1">'.$row["title"].'</td>';
    echo '<td class="sitename2"><div class="pointlink2">'.number_format($row["points"]).'</div></td>';
    echo '</tr>';
}
echo "</table>";

如果几行共享相同的"点"值，这有点棘手：

points | title | submissionid
------ + ----- + ------------
    ...
   50  | 'foo' | ABCD01234     <-- If (50, 'foo') is the "midpoint" record,
   50  | 'bar' | EF7654321     <-- does (50, 'bar') come before or after?
    ...

在这种情况下，我们需要施加命令。为方便起见，我们将按"点"降序排序，然后按"标题"降序排序。

假设您的"中点记录"的积分值为"@points"，标题为"@title"，我们将说中点"之前"的记录是那些(点，标题(>(@points，@title(的记录。同样，中点"之后"的那些记录有其(点，标题(<(@points，@title(。

综合起来，我们有：

-- First, initialize user variables with the points and
-- title of our midpoint (including midpoint)
--
SELECT @title := title,
       @points := points
  FROM submission
 WHERE title = ?          -- bind your $submission variable bere
 LIMIT 1;
-- Now, select six records greater than or equal to our
-- midpoint.
--
  SELECT title, points, submissionid
        FROM (  SELECT title, points, submissionid
                  FROM submission
                 WHERE (points, title) >= (@points, @title)
              ORDER BY points ASC, title ASC
                 LIMIT 6) gte
-- and UNION those records with five records less than
-- our midpoint
--
   UNION
  SELECT title, points, submissionid
        FROM (  SELECT title, points, submissionid
                  FROM submission
                 WHERE (points, title) < (@points, @title)
              ORDER BY points DESC, title DESC
                 LIMIT 5) lt
-- Finally sort the result set
--
ORDER BY points DESC, title DESC

你需要使用UNION

(
    SELECT title, points, submissionid
    FROM submission
    WHERE points < (SELECT points FROM submission WHERE title = <row_title> LIMIT 1)
    ORDER BY points DESC LIMIT 5
)UNION(
    SELECT title, points, submissionid
    FROM submission
    WHERE points > (SELECT points FROM submission WHERE title = <row_title> LIMIT 1)
    ORDER BY points ASC LIMIT 5
) ORDER BY points DESC

我还没有测试过它，但这是它的要点。你会得到 10 条记录(或更少(，你必须在 PHP 中弄清楚哪些记录应该高于你的$submission，哪些应该低于你的记录，因为如果你得到 9 条记录，你不会知道 4 条更高，还是 5 条。

或者，您可以通过 2 个查询来完成它，当然是>_>

SELECT 
    title, points, submissionid
FROM
    submission 
WHERE 
    ROWID >= (SELECT ROWID FROM submission WHERE ..... ORDER BY points DESC) - 5
AND
    ROWID <= (SELECT ROWID FROM submission WHERE ..... ORDER BY points DESC) + 5
ORDER BY 
    points DESC

我认为

以下内容可能会满足您的要求。它将运行查询并开始从设置的结果中读取记录，直到找到标题等于您拥有的$submission变量的记录的第一次出现(如果我理解正确，您表的主键是 submitid，标题是一个简单的字段 - 即您没有唯一键，因此，可能有多个具有相同标题的记录(。

找到第一条记录后，它将再读取 5 条记录并停止。然后，它将为您提供要打印的记录数组的一部分，最后它将打印它。

$sqlStr = "SELECT title, points, submissionid FROM submission ORDER BY points DESC"; 
$result = mysql_query($sqlStr);
$count = 1;
$found = false;
$continue = true;
$records = array();
$row = mysql_fetch_array($result);
while ($row !== false && $continue === true)
{ 
    if($found == false && $row['title'] == $submission)
    {
        $found = true;
    }
    elseif($found == true && $count < 6) 
    {
        $count++;
    }
    elseif($found == true && $count >= 6)
    {
        $continue = false;
    }
    $records[] = $row;
    $row = mysql_fetch_array($result);
}
if($found === true)
{
    if(array_count($records) > 11)
        $records = array_splice($records, -11);
}
else
{
    $records = array();
}
echo "<table class='"samplesrec'">";
for($i = 1; $i <= count($records); $i++)
{
    echo '<tr >';
    echo '<td>'.$i.'.</td>';
    echo '<td class="sitename1">'.$records[$i]["title"].'</td>';
    echo '<td class="sitename2"><div class="pointlink2">'.number_format($records[$i]["points"]).'</div></td>';
    echo '</tr>';
}
echo "</table>";

如果没有重复的"title"值，并且没有重复的"points"

SELECT title, points, submissionid
FROM submission 
WHERE points <=
      ( SELECT points
        FROM submission
        WHERE points >= 
              ( SELECT points
                FROM submission
                WHERE title = @row_title
              )
        ORDER BY points ASC
        LIMIT 1 OFFSET 5
      )
ORDER BY points DESC
LIMIT 11 OFFSET 0

我认为对

数据库进行 2 次查询是最简单的，但你可以在 PHP 中做到这一点：

$entryFound = false;
$counter = 0;
$priorEntries = array();
while ($row = mysql_fetch_array($result)) { 
    $rowHtml = '<tr >';
    $rowHtml .=  '<td>'.$count++.'.</td>';
    $rowHtml .= '<td class="sitename1">'.$row["title"].'</td>';
    $rowHtml .= '<td class="sitename2"><div class="pointlink2">'.number_format($row["points"]).'</div></td>';
    $rowHtml .= '</tr>';
    if ($entryFound) {
            if ($counter < 5) {
                $counter++;
                echo $rowHtml;
            }
    } else {            
            array_unshift($priorEntries, $rowHtml); 
            if (strcmp($row["title"], $submission) == 0) {
                echo implode(array_reverse($priorEntries));
                $entryFound = true;
            }   
    array_splice($priorEntries, 5);
    }
}

如果意图是使用单个查询并且表很小并且性能无关紧要，则使用

SELECT b.title, b.points FROM (
  SELECT @rank1 := @rank1 + 1 as slno, temp1.* FROM (
    SELECT s1.title, s1.points, COUNT(s2.title) rank
    FROM submission s1
    JOIN submission s2 ON s1.points <= s2.points
    GROUP BY s1.title, s1.points
    ORDER BY rank, s1.title ASC
  ) as temp1 
  JOIN( SELECT @rank1 := 0 ) AS init
  ORDER BY slno 
) a
LEFT JOIN (
  SELECT @rank2 := @rank2 + 1 as slno, temp1.* FROM (
    SELECT s1.title, s1.points, COUNT(s2.title) rank
    FROM submission s1
    JOIN submission s2 ON s1.points <= s2.points
    GROUP BY s1.title, s1.points
    ORDER BY rank, s1.title ASC
  ) as temp1 
  JOIN( SELECT @rank2 := 0 ) AS init
  ORDER BY slno 
) b ON a.slno BETWEEN b.slno - 5 AND b.slno + 5
WHERE a.title = <row_title>;

这将返回上面最多 5 个(

如果存在(和下面最多 5 个(如果存在(选择的行。

但是，强烈建议使用临时表来存储排名并使用它来显示排名。

有关上述查询的更多详细信息 @http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#460

有关优化方法 @ 的更多详细信息http://onlamp.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html