例如：
iD A
1 10
1 34
1 54
2 23
2 45
3 33
查詢后 ：
iD A B
1 10 98
1 34 98
1 54 98
2 23 68
2 45 68
3 33 33
uj5u.com熱心網友回復：
沒有你的表，我自己新建了一個臨時表，你直接執行以下陳述句即可。SELECT T.*,(SELECT SUM(T1.A) FROM (
SELECT '1' ID,'10' A FROM DUAL UNION ALL
SELECT '1','34' FROM DUAL UNION ALL
SELECT '1','54' FROM DUAL UNION ALL
SELECT '2','23' FROM DUAL UNION ALL
SELECT '2','45' FROM DUAL UNION ALL
SELECT '3','33' FROM DUAL ) T1 WHERE T1.ID = T.ID) B FROM (
SELECT '1' ID,'10' A FROM DUAL UNION ALL
SELECT '1','34' FROM DUAL UNION ALL
SELECT '1','54' FROM DUAL UNION ALL
SELECT '2','23' FROM DUAL UNION ALL
SELECT '2','45' FROM DUAL UNION ALL
SELECT '3','33' FROM DUAL ) T ;
uj5u.com熱心網友回復：
我這個表是程序中創建出來的零時表，用你這個方法，它提示Can't reopen table: 'T1'。不能重復讀取，怎么辦？uj5u.com熱心網友回復：
把你修改后的陳述句，發來看看。uj5u.com熱心網友回復：
把你執行報錯的陳述句，發來看看。uj5u.com熱心網友回復：
分析函式不就ok了嗎WITH tmp AS (
SELECT
1 AS p_id,
10 AS p_col UNION ALL
SELECT
1,
34 UNION ALL
SELECT
1,
54 UNION ALL
SELECT
2,
23 UNION ALL
SELECT
2,
45 UNION ALL
SELECT
3,
33
) SELECT
tmp.* ,sum(p_col)over(partition by p_id order by p_id ) p_sum
FROM
tmp;
uj5u.com熱心網友回復：
樓上的，發錯地方了吧，那個是ORACLE的語法，MYSQL跑不過去的。uj5u.com熱心網友回復：
第一步：添加一個total列作為總分；alter table 表名 add total int;
第二步：計算；
update 表名 set total=(A+B)；
uj5u.com熱心網友回復：
SELECT t1.ID, t1.score, t2.sum_socre
FROM myt
JOIN (
SELECT ID, SUM(score) AS sum_socre
FROM myt
GROUP BY ID
) t2
ON t1.ID = t2.ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/51369.html
標籤：MySQL