mysql - 粘附总和小计 mysql crosstab by percentage

Question

我想根据每个项目的值(value)除以每个组的总和来计算百分比。

这是我的代码:

 SET @VARBRANCH='
            MAX(IF(Branch =''A'',`data`, NULL)) AS ''A'',
            MAX(IF(Branch =''B'',`data`, NULL)) AS ''B''';

            DROP TEMPORARY TABLE IF EXISTS TBL_ACBR;
            SET @VARBRANCH=CONCAT('CREATE TEMPORARY TABLE TBL_ACBR SELECT Prtype,SUM(data) AS Total,  '
            ,@VARBRANCH,
            ' FROM(
            SELECT Prtype, Branch, `data` FROM TBLGETDATAALL_1 GROUP BY  Branch,Prtype) t
            GROUP BY Prtype;');
            PREPARE stmacbr FROM @VARBRANCH;
            EXECUTE stmacbr;
            DEALLOCATE PREPARE stmacbr; 

            SELECT * FROM TBL_ACBR;


Script above it show as the following:

Prtype Total  A  B
JAM     5     3  2  
Jim     6     3  3

The result that i need as the following by percentage:

Prtype Total     A      B
Total   11       6      5
JAM     5/11     3/6    2/5  
Jim     6/11     3/6    3/5

Answer 1

试试这个:

 SET @VARBRANCH='
        MAX(IF(Branch =''A'',`data`, NULL)) AS ''A'',
        MAX(IF(Branch =''B'',`data`, NULL)) AS ''B''';

        DROP TEMPORARY TABLE IF EXISTS TBL_ACBR;
        SET @VARBRANCH=CONCAT('CREATE TEMPORARY TABLE TBL_ACBR SELECT Prtype,SUM(data) AS Total,  '
        ,@VARBRANCH,
        ' FROM(
        SELECT Prtype, Branch, `data` FROM TBLGETDATAALL_1 GROUP BY  Branch,Prtype) t
        GROUP BY Prtype;');
        PREPARE stmacbr FROM @VARBRANCH;
        EXECUTE stmacbr;
        DEALLOCATE PREPARE stmacbr; 

        -- New queries
        SELECT SUM(A), SUM(B) INTO @SUM_A, @SUM_B FROM TBL_ACBR; -- edit
        UPDATE TBL_ACBR SET A = A / @SUM_A, B = B/@SUM_B;
        INSERT INTO TBL_ACBR (Prtype, Totel, A, B) 
          VALUES ("Total", @SUM_A+@SUM_B, SUM_A, SUM_B);

        SELECT * FROM TBL_ACBR;