如何在SQL查询中将select的结果用作偏移量

时间：2021-09-02 23:32:25

I was trying to create a single SQL query to return what I need, instead of creating 2 querys, but I need to use the result of one of the querys as the offset of the other one.

我试图创建一个单独的SQL查询来返回我需要的内容,而不是创建2个查询,但我需要使用其中一个查询的结果作为另一个查询的偏移量。

My table has user answers with scores, each user may have multiple answers in that table. And I want to calculate the middle point of the table of ordered scores.

我的表有用户答案和分数,每个用户可能在该表中有多个答案。我想计算有序分数表的中间点。

Example:

User answers:

  1. User 1 - 5 points

    2. 用户1 - 5分

  2. User 1 - 15 points

    3. 用户1 - 15分

  3. User 2 - 8 points

    4. 用户2 - 8分

  4. User 3 - 12 points

    5. 用户3 - 12分

Ranking Table:

  1. User 1 - 20 points

    2. 用户1 - 20分

  2. User 3 - 12 points < Middle point

    3. 用户3 - 12分 <中点< p>

  3. User 2 - 8 points

    4. 用户2 - 8分

Solution:

The first query calculates the middle point:

第一个查询计算中间点:

SELECT CEILING(count(Distinct(id_user)) / 2) as position 
FROM us_user_response 
where id_round=1

Query result:

position : 2

职位:2

This second query creates the ordered ranking table:

第二个查询创建有序排名表:

SELECT sum(points) as score 
FROM us_user_response 
where id_round=1 
GROUP BY id_user 
Order by score DESC

Now I want to create one big query that returns the score of the middle user, I just need to use the first query result as offset of the second query:

现在我想创建一个返回中间用户得分的大查询,我只需要将第一个查询结果用作第二个查询的偏移量:

SELECT sum(points) as score 
      FROM us_user_response 
      where id_round=1
      GROUP BY id_user 
      Order by score DESC LIMIT 1
        OFFSET (SELECT CEILING(count(Distinct(id_user)) / 2) as position 
                FROM us_user_response where id_round=1)

Of course, this doesn't work, is there any way to use a result as offset?

当然,这不起作用,有没有办法将结果用作偏移?

EDIT:

The queries work nice! My question is if there is any way to use the result of a query as the offset of another. So I could accomplish this in one single query.

查询效果很好!我的问题是,是否有任何方法可以将查询结果用作另一个查询的结果。所以我可以在一个查询中完成此任务。

1 个解决方案

#1

1  

Try something like this:

尝试这样的事情:

SET @line_id = 0;
SET @line_offset = (
    SELECT CEILING(count(Distinct(id_user)) / 2) as position 
    FROM us_user_response 
    WHERE id_round = 1
);

SELECT sum(points) as score,
    IF((@line_id := @line_id + 1) = @line_offset, 1, 0) AS useit 
FROM us_user_response 
WHERE id_round = 1
GROUP BY id_user 
HAVING useit = 1
ORDER BY score;

#1

1  

Try something like this:

尝试这样的事情:

SET @line_id = 0;
SET @line_offset = (
    SELECT CEILING(count(Distinct(id_user)) / 2) as position 
    FROM us_user_response 
    WHERE id_round = 1
);

SELECT sum(points) as score,
    IF((@line_id := @line_id + 1) = @line_offset, 1, 0) AS useit 
FROM us_user_response 
WHERE id_round = 1
GROUP BY id_user 
HAVING useit = 1
ORDER BY score;
标签：

相关文章