sql显示基于mysql计算列的分区

问题描述

假设我们有这种关系:

╔═══════════════════╗
║ i++ name  score   ║
╠═══════════════════╣
║ 1   Will  123     ║
║ 2   Joe   100     ║
║ 3   Bill  99      ║
║ 4   Max   89      ║
║ 5   Jan   43      ║
║ 6   Susi  42      ║
║ 7   Chris 11      ║
║ 8   Noa   9       ║
║ 9   Sisi  4       ║
╚═══════════════════╝

现在我需要一个基于我正在搜索的数据的子集.例如，我正在寻找第五个地方.在我的结果中，我需要的不仅仅是 Jan 的记录，我还需要 Jan 之前的两条记录以及 Jan 后面的两条记录.所以我有以下结果集:

Now I need a subset based on the data I am searching for.For instance I'm searching for the fith place.In my result I need more than the record of Jan, I need the two records before Jan and the two records behind Jan too. So I have the following resultset:

╔═══════════════════╗
║ id++ name score   ║
╠═══════════════════╣
║ 3   Bill  99      ║
║ 4   Max   89      ║
║ 5   Jan   43      ║
║ 6   Susi  42      ║
║ 7   Chris 11      ║
╚═══════════════════╝

这是我得到的sql:

select @a:= id from quiz.score where username = 'Jan';
set @i=0;
SELECT @i:=@i+1 as Platz, s.*
FROM quiz.score s where id BETWEEN @a-5 AND @a+5
order by points desc;

这里的问题是 @a 是记录的 id.有没有办法使用计算值@i:=@i+1?

The problem here is that @a is the id of the record. Is there a way to use the calculated value @i:=@i+1?

非常感谢您的帮助.

推荐答案

如果您的输出中不需要排名(并且从您的评论和喜欢的答案中可以看出您不需要)，您可以简单地合并测验分数最接近 Jan 的分数:

If you do not need the rank in your output (and it appears from your comments and favored answers that you do not), you can simply combine the quiz scores nearest to Jan's score:

查询(此处为 SQL Fiddle):

-- XXX this assumes `scores`.`username` is UNIQUE !
SELECT * FROM (
    -- Get those who scored worse (or tied)
    (    SELECT s.*
           FROM scores s
     CROSS JOIN (SELECT score FROM scores WHERE username = 'Jan') ref
          WHERE s.score <= ref.score AND username <> 'Jan'
       ORDER BY s.score DESC
          LIMIT 2)
    UNION
    -- Get our reference point record
    (SELECT s.* FROM scores s WHERE username = 'Jan')
    UNION
    -- Get those who scored better
    (    SELECT s.*
           FROM scores s
     CROSS JOIN (SELECT score FROM scores WHERE username = 'Jan') ref
          WHERE s.score > ref.score AND username <> 'Jan'
       ORDER BY s.score ASC
          LIMIT 2)
) slice
ORDER BY score ASC;

(请注意，我将结果限制为 1 月之前的两条记录和 1 月之后的两条记录，因为您的样本数据集太小了.)

(Note that I LIMITed the results to two records before Jan and two after Jan because your sample data set was so small.)

需要上述构成查询的括号才能让 LIMIT 和 UNION 协同工作.然后最外层的查询让我们ORDER UNION 的结果.

Those parentheses on the constituent queries above are needed to allow LIMIT and UNION to work together. The outermost query then lets us ORDER the results of a UNION.

这篇关于sql显示基于mysql计算列的分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！