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 ;
分析函数不就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;
楼上的,发错地方了吧,那个是ORACLE的语法,MYSQL跑不过去的。
第一步:添加一个total列作为总分; alter table 表名 add total int; 第二步:计算; update 表名 set total=(A+B);
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
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 ;
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;
alter table 表名 add total int;
第二步:计算;
update 表名 set total=(A+B);
FROM myt
JOIN (
SELECT ID, SUM(score) AS sum_socre
FROM myt
GROUP BY ID
) t2
ON t1.ID = t2.ID