mysql查询需要优化

I have a query which give result like

我有一个查询，给出结果如

id | productid | userid | coinsid
1  | 2         | 2      |  5     
3  | 2         | 2      |  6      
4  | 2         | 3      |  7
5  | 2         | 4      |  8
6  | 2         | 3      |  9

This is result for specific productid. Now i have to update the balance in user table by adding $1 to all the users in above result, but if userid is twice, i need to add $1 twice to the balance of that specific user. So in the above case $1 twice added to userid=2 balance and userid=3 balance.

这是特定产品的结果。现在我必须通过向上述结果中的所有用户添加$ 1来更新用户表中的余额，但如果userid是两次，我需要将两次$ 1添加到该特定用户的余额中。所以在上面的情况下，$ 1两次添加到userid = 2 balance和userid = 3 balance。

The simple way is to count records for every distinct userid and run queries as many time as we have users in foreach loop. But i am looking for some optimize way. Please suggest any. Thanks

简单的方法是为每个不同的用户ID计算记录，并像在foreach循环中使用用户一样多次运行查询。但我正在寻找一些优化方式。请建议任何。谢谢

4 个解决方案

#1

One approach:

一种方法：

UPDATE user_table u
  JOIN ( SELECT q.userid
              , SUM(1.00) AS deposit
           FROM (
                  -- original OP query goes here
                ) q
          GROUP BY q.userid
       ) r
    ON r.userid = u.userid 
   SET u.balance = u.balance + r.deposit

We use the original OP query that returns the resultset displayed, and make that an inline view (aliased in the query above as q).

我们使用返回显示的结果集的原始OP查询，并使其成为内联视图（在上面的查询中别名为q）。

From that, we query a distinct list of userid, and the number of times that userid appears in the resultset. That gives us the username and a deposit amount (1 dollar for each time the userid appears) (some databases might want us to specify the value as 1.0 rather than 1, to make sure it was decimal. I think the SUM is more representative of what we are trying to accomplish.)

从那里，我们查询userid的不同列表，以及userid在结果集中出现的次数。这给了我们用户名和存款金额（每次出现用户ID时1美元）（某些数据库可能希望我们将值指定为1.0而不是1，以确保它是十进制的。我认为SUM更具代表性我们想要实现的目标。）

We join that inline view (r) to the user table, and add the deposit amount to the current balance, for that user (assuming the balance is stored as decimal dollars (1.00 = one dollar)

我们将内联视图（r）加入到用户表中，并将存款金额添加到该用户的当前余额中（假设余额存储为十进制美元（1.00 =一美元）

To testing, convert the UPDATE into a SELECT statement:

要进行测试，请将UPDATE转换为SELECT语句：

remove the "SET" clause
删除“SET”子句
add an "ORDER BY" clause (optional) to make the results determinate
添加“ORDER BY”子句（可选）以使结果确定
remove the "UPDATE" keyword and replace it
删除“UPDATE”关键字并替换它

with:

有：

 SELECT r.userid
      , r.deposit
      , u.balance             AS old_balance
      , u.balance + r.deposit AS new_balance
      , u.userid
   FROM

Full select:

全选：

 SELECT r.userid
      , r.deposit
      , u.balance             AS old_balance
      , u.balance + r.deposit AS new_balance
      , u.userid
   FROM user_table u
  JOIN ( SELECT q.userid
              , SUM(1.00) AS deposit
           FROM (
                  -- original OP query goes here
                ) q
          GROUP BY q.userid
       ) r
    ON r.userid = u.userid

NOTE There is no WHERE clause, the JOIN predicates (in the ON clause) is what determines which rows are selected/affected in the user table.

注意没有WHERE子句，JOIN谓词（在ON子句中）决定了在用户表中选择/影响哪些行。

#2

Assuming you have no duplicate user ids in your balance table, maybe something like this would work:

假设您的余额表中没有重复的用户ID，也许这样的东西可以工作：

update balance_table set balance_table.balance = (select count(*) from users_table where users_table.user_id = balance_table.user_id) * 1;

I haven't tried this query against a mysql database as I am more familiar with plsql, but wouldn't something like this work ?

我没有尝试过对mysql数据库的这个查询，因为我对plsql更熟悉，但不会像这样工作吗？

#3

The correlated subquery in the other answer will work, but an INNER JOIN will usually be more efficient. Try something like this; you'll of course need to supply the table and column names.

另一个答案中的相关子查询将起作用，但INNER JOIN通常会更有效。尝试这样的事情;你当然需要提供表名和列名。

UPDATE myTable
INNER JOIN (
  SELECT userid, count(*) AS AmountToAdd
  FROM users
  GROUP BY userid
) UserCounts ON myTable.userid = UserCounts.userid
SET balance = balance + UserCounts.AmountToAdd

#4

select count(*), userid from yourTable group by userid

If I do understand your question.

如果我确实理解你的问题。

#1