For example:
ID A
1 10
1 34
1
542 23
2 45
3 33

After the query:
ID A, B
1 October 98
1 34 98
1, 54, 98
2 23 68
2 45 68
3, 33, 33

CodePudding user response:

Without your table, I built a temporary table, you perform the following statements can be directly,

SELECT T. *, (SELECT SUM (T1) A) FROM (
SELECT '1' ID, '10' FROM A 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=B T.I D) FROM (
SELECT '1' ID, '10' FROM A 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;

CodePudding user response:

reference 1st floor AHUA1001 response:

without your table, I built a temporary table, you perform the following statements can be directly,

SELECT T. *, (SELECT SUM (T1) A) FROM (
SELECT '1' ID, '10' FROM A 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=B T.I D) FROM (
SELECT '1' ID, '10' FROM A 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;

I this table is created in the process of temporary table, use your this method, it prompts Can 't reopen table:' T1 ', read cannot be repeated, how to do?

CodePudding user response:

Your revised statement, a look,

CodePudding user response:

Your statement execution error, a look,

CodePudding user response:

Analysis function is not ok

AS WITH TMP (
SELECT
1 the 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
The FROM
TMP.

CodePudding user response:

Upstairs, send the wrong place, that is the grammar of the ORACLE, MYSQL can't run in the past,

CodePudding user response:

Step 1: add a total column as the total score;
The alter table table name add total int.
Step 2: calculate;
The update table name set total=(A + B);

CodePudding user response:

 SELECT t1. ID, t1) score, t2. Sum_socre 
The FROM myt 
The JOIN (
SELECT ID, the SUM (score) AS sum_socre 
The FROM myt 
GROUP BY ID 
T2) 
ON t1. ID=t2. ID