홈>2020-09-23 09:00source

MySQL 쿼리 결과에 "전체"행을 더하고 빼는 방법

문제 :

따라서 다음과 같은 두 개의 테이블이 있습니다 :

main_table:                      property_table:
+----------------------------+   +-------------------------+
| event| property_id | value |   | property_id | name      | 
+----------------------------+   +-------------------------+
| 1    | 1           | 100   |   | 1           | property1 |
| 2    | 1           | 200   |   | 2           | property2 |
| 3    | 2           |  50   |   | 3           | property3 |
| 4    | 3           |  10   |   +-------------+-----------+
| 5    | 3           |  30   |

그리고 다음 쿼리를 사용합니다 :

SELECT pt.name, SUM(value) as subtotal                                             
    FROM main_table as mt                                                              
LEFT JOIN property_table as pt                                                         
    ON mt.property_id = pt.property_id                                             
GROUP BY pt.name;

다음 표를 얻습니다 :

+-------------------------+
| name         | subtotal |                 
---------------------------
| property1    | 300      |
| property2    |  50      |
| property3    |  40      |

이제이 표에 행을 추가하여 모든 속성 중 총계를 계산하려고합니다.그러나 여기에는 캐치가 있으며 일부 속성을 추가하고 일부를 빼야합니다. 따라서 논쟁을 위해total = property1-property2 + property3이라고 가정하겠습니다.

예상 출력 :

+-------------------------+
| name         | subtotal |                 
---------------------------
| property1    | 300      |
| property2    |  50      |
| property3    |  40      |
| total        | 290      |

참고 사항 :

이제 "GROUP BY"는 "WITH ROLLUP"과 함께 사용할 수 있지만 모든 값을 독점적으로 추가합니다. 기술적으로 테이블에 음수를 허용하고 "WITH ROLLUP"으로 모든 것을 해결할 수는 있지만 사용자 오류가 발생하기 쉬운 것 같습니다. 음의 정수를 피하기 위해 상단에 있습니까?

mysql

답변 # 1

모든 조합을 기반으로 하위 쿼리를 사용할 수 있습니다 하나는 값을 선택하고 하나는 전체를 선택합니다

select name, subtotal
from  (
    SELECT pt.name, SUM(value) as subtotal , 0  ord                                        
    FROM main_table as mt                                                              
    LEFT JOIN property_table as pt ON mt.property_id = pt.property_id                                             
    GROUP BY pt.name
    union  all
    SELECT 'Total', SUM(value) , 1                                           
    FROM main_table as mt                                                              
    LEFT JOIN property_table as pt ON mt.property_id = pt.property_id                                             
    group by 1
) t 
order by ord, name

답변 # 2

결국 GROUP BY WITH ROLLUP을 사용할 때 SUM 내부에서 사례를 구현할 수 있음을 발견했습니다.

   SELECT  pt.name,
            SUM(
                CASE
                    WHEN pt.name = 'property2' THEN -mt.value
                    ELSE mt.value
                END
            ) as subtotal
    FROM    main_table as mt
    LEFT JOIN property_table as pt 
        ON mt.property_id = pt.property_id
    GROUP BY pt.name WITH ROLLUP;

그러나 실제 구현은 action이라는 열을 추가하기로 결정했습니다.

property_table:
+-------------------------+---------+
| property_id | name      | action  |
+-------------------------+---------+
| 1           | property1 |   '+'   |
| 2           | property2 |   '-'   |
| 3           | property3 |   '+'   |
+-------------+-----------+---------+

Case를 IF로 교체하십시오 :

   SELECT  pt.name,
            SUM(IF(pt.action='-', -mt.value, mt.value) as subtotal
    FROM    main_table as mt
    LEFT JOIN property_table as pt 
        ON mt.property_id = pt.property_id
    GROUP BY pt.name WITH ROLLUP;

Python2.net

MySQL 쿼리 결과에 "전체"행을 더하고 빼는 방법

관련 자료