显示查询中的连续11行，其中中间行包含变量

问题描述

在我的网页上，有一个名为$submission的变量.我想从下面的查询中准确显示11行:$submission等于$row["title"]的行，其上方的5行和其下方的5行.全部按points降序排列.

On my web page, there is a variable called $submission. I would like to display exactly 11 rows from the query below: the row where $submission equals $row["title"], 5 rows above it, and 5 rows below it. All ranked by points descending.

我该怎么做?

$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>";

推荐答案

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

This is a little tricky if several rows share the same value for "points":

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

在这种情况下，我们需要下订单.为了方便起见，我们将按点"降序排列，然后按标题"降序排列.

In this case, we need to impose an order. For convenience we're going to order by "points" descending and then "title" descending.

假设您的中点记录"的点值为"@points"，标题为"@title"，那么我们说中点之前"的记录是其(点，标题)>( @ points，@ title).类似地，在中点之后"的那些记录具有其(点，标题)<. (@ points，@ title).

Assuming your "midpoint record" has a points value of '@points' and a title '@title', we'll say the records that come "before" the midpoint are those records whose (points, title) > (@points, @title). Similarly, those records "after" the midpoint have their (points, title) < (@points, @title).

将其放在一起，我们有:

Putting it together, we have:

-- 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

这篇关于显示查询中的连续11行，其中中间行包含变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！