>source
문제 :

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

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"으로 모든 것을 해결할 수는 있지만 사용자 오류가 발생하기 쉬운 것 같습니다. 음의 정수를 피하기 위해 상단에 있습니까?


  • 답변 # 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;
    
    

관련 자료

  • 이전 python - 전문가 명상 오류 - mqtt를 사용하는 코어 1 패닉 (이중 예외) esp32
  • 다음 typescript - JavaScript에서 재귀 메서드 호출