使用AS避免相同的查询mySQL [英] using AS to avoid same query mySQL

问题描述

我正在尝试获取一半好玩家的ID，但仅获取node_id = 28中包含的ID.

I am trying to get the IDs of half of the good players, but only those contained in node_id=28

" SELECT * FROM 
        (SELECT npid.player_id  
        FROM node_to_player_ids    npid

            INNER JOIN  players_gameplay_info    pg
            ON  npid.player_id = pg.player_id

        WHERE  npid.node_id = 28
        ORDER BY  pg.score_collected ASC)  AS selectedPlayers
   LIMIT  (SELECT COUNT(*) FROM selectedPlayers) / 2"

大约有100万个条目要计数，因此我想将其存储在一个变量中，但是，我们不能像@myLocalVariable

There will be approximately 1000 000 entries to count, so I wanted to store it in a variable, however, we cannot store multiple rows in the one like @myLocalVariable

只能存储一条记录.但是，应该可以解释选择AS selectedPlayers

Only one record could be stored. However, it should be possible to interpret the selection AS selectedPlayers

那样，我希望我会重用获得的大量player_ids并对其进行迭代，而不是执行相同的选择查询只是计算记录的一半

That way I hoped I would re-use the obtained mass of player_ids and iterate over them rather than performing the same selection query just to count half the records

但是，我遇到一个错误

'SQLSTATE[42000]: Syntax error or access violation: 1064 near '(SELECT COUNT(*) FROM selectedPlayers) / 2' at line 10'

推荐答案

https://dev.mysql.com/doc/refman/5.7/zh-CN/select.html 说:

LIMIT子句可用于约束SELECT语句返回的行数. LIMIT接受一个或两个数字参数，这些参数都必须是非负整数常量，但以下情况除外:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• 在准备好的语句中，可以使用?指定LIMIT参数.占位符标记.
• 在存储的程序中，可以使用整数值的例程参数或局部变量来指定LIMIT参数.

换句话说，LIMIT不允许将其参数用作列名，子查询或表达式.

In other words, LIMIT does not allow its argument to be a column name, subquery, or expression.

您必须在两个查询中执行此操作.首先获得您想要的数量:

You'll have to do this in two queries. The first to get the count you want:

SELECT FLOOR(COUNT(*)/2) AS player_count FROM selectedPlayers;

使用文字数字参数或查询参数，对您的原始查询的第二个查询，应用了LIMIT:

The second for your original query, with LIMIT applied, using a literal numeric argument or query parameter:

SELECT npid.player_id  
FROM node_to_player_ids AS npid
INNER JOIN  players_gameplay_info AS pg
  ON npid.player_id = pg.player_id
WHERE npid.node_id = 28
ORDER BY pg.score_collected ASC
LIMIT <player_count>

在我写<player_count>的地方，您可以通过插入整数值或使用会话变量或查询参数将其替换为第一个查询的结果.或者，如果您在存储过程中编写此查询，则可以DECLARE局部变量.

Where I wrote <player_count> you would replace that with the result from the first query, either by interpolating the integer value, or by using a session variable or query parameter. Or if you're writing this query in a stored procedure you can DECLARE a local variable.

如果您使用的是phpMyAdmin，请注意，会话变量在两次请求之间不会保留，因为每个请求都会启动一个新的会话.

If you're using phpMyAdmin, note that session variables do not survive between requests, because each request starts a new session.

这篇关于使用AS避免相同的查询mySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！