如何使用 SUM() 和 COUNT() 将同一表中的表更新到不同的列 [英] How to UPDATE table with SUM() and COUNT() in same table to different columns

问题描述

我目前没有为我的网页执行此操作以显示表格中的数据，现在加载页面需要更长的时间，因为越来越多的数据进入数据库，这使得查询耗时.在我的脑海中，我希望按如下方式更新表格，以便我只是查询表格，而不是通过 SELECT 函数对内容运行额外的心律失常.我计划在 while 循环中运行类似的东西来循环 user 变量的所有迭代.

I currently dont do this for my webpage for displaying data from the table, and it is now taking longer to load the page as more and more data enters the database making the query time consuming. In my head, I am looking to update a table as follows so that I am just querying the table and not running additional arrhythmic on the contents via SELECT functions. I was planning to run something like this in a while loop to cycle all iterations of the user variable.

UPDATE table
SET total = SUM(sales)
SET orders = COUNT(order)
GROUP by user
WHERE user=$id

推荐答案

尝试使用 left join (sum + count) 表中的更新值

try it use update value from left join (sum + count) table

update T T1
  left join (
    select `user`,sum(`sales`) newtotal,count(`order`) neworders
    from T
    group by `user`
  ) T2 on T1.`user` = T2.`user`
set T1.total = T2.newtotal,T1.orders = T2.neworders

<小时>

测试 DDL:

CREATE TABLE T
    (`user` varchar(4), `sales` int, `order` varchar(7), `total` int, `orders` int)
;

INSERT INTO T
    (`user`, `sales`, `order`, `total`, `orders`)
VALUES
    ('xx01', 100, 'order01', 0, 0),
    ('xx02', 200, 'order02', 0, 0),
    ('xx02', 400, 'order03', 0, 0),
    ('xx03', 300, 'order04', 0, 0),
    ('xx03', 500, 'order05', 0, 0)
;

<小时>

结果:

| user | sales |   order | total | orders |
|------|-------|---------|-------|--------|
| xx01 |   100 | order01 |   100 |      1 |
| xx02 |   200 | order02 |   600 |      2 |
| xx02 |   400 | order03 |   600 |      2 |
| xx03 |   300 | order04 |   800 |      2 |
| xx03 |   500 | order05 |   800 |      2 |

这篇关于如何使用 SUM() 和 COUNT() 将同一表中的表更新到不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！