SQL Filter on sum

Вот мой запрос, как он стоит, и он работает:

SELECT EXPENDITURES.CNTRT, 
EXPENDITURES.L_CNTRT, 
EXPENDITURES.BE, 
EXPENDITURES.L_BE, 
EXPENDITURES.SPGM_STD, 
EXPENDITURES.L_SPGM, 
EXPENDITURES.CAT, 
EXPENDITURES.L_CAT, 
EXPENDITURES.OCA, 
EXPENDITURES.L_OCA, 
EXPENDITURES.L2, 
EXPENDITURES.L1L5, 
EXPENDITURES.L_L1L5, 
EXPENDITURES.OBJ, 
EXPENDITURES.L_OBJ, 
EXPENDITURES.STFY, 
EXPENDITURES.CF, 
EXPENDITURES.TRNS_AMT, 
EXPENDITURES.MGDT, 
EXPENDITURES.VENDOR_ID_NO, 
EXPENDITURES.VENDOR_LONG_NAME, 
EXPENDITURES.DESCRIPTION, 
EXPENDITURES.INVOICE_NO, 
EXPENDITURES.DN, 
EXPENDITURES.OTHER_DOC_NO, 
EXPENDITURES.PRIM_DOC_NO, 
EXPENDITURES.SECOND_DOC_NO, 
EXPENDITURES.TR
 FROM IDS.EXPENDITURES EXPENDITURES
 WHERE ((EXPENDITURES.BE<>'70212349')) 
AND (EXPENDITURES.OCA LIKE '%C') 
AND (EXPENDITURES.STFY='2017') 
AND (EXPENDITURES.CF<>'C') 
AND ((EXPENDITURES.MGDT<={ts '2016-07-31 00:00:00'}) AND (EXPENDITURES.MGDT>={ts '2016-07-01 00:00:00'})) 

То, что меня попросили сделать, это сделать так, чтобы, если вы только группируете поля до CF, sum on TRNS_AMT на основе этого и sum of TRNS_AMT <> 0, то возвращали все другие данные по этим транзакциям.

Я не человек SQL и должен был сделать все это вручную и был бы признателен за любую помощь в том, что мне нужно сделать. Я попытался сделать наличие и GROUP BY, но это не позволило бы мне пропустить все поля после TRNS_AMT из GROUP BY, и все эти поля после GROUP BY вызывают проблему, потому что они не идентичны для любой транзакции, таким образом, не приводя к фильтрации на TRNS_AMT.


Итак, на английском языке мне нужно сначала найти какие транзакции, суммы которых сгруппированы по первым 17 полям, не равны нулю и вернуть все 28 полей на ненулевые результаты, с их trns_amt unsmed, пользователю.

2 ответа

  1. Вы не упомянули, какие СУБД вы запрашиваете. Я думаю, что ниже будет работать для SQL server 2012 или новее и, возможно, oracle, но я не уверен.

    Я использую оконную сумму, чтобы выбрать сумму транс, сгруппированную по требуемым столбцам, а затем выбрать, где это больше 0.

    Вы также можете использовать обычную сумму в cte и присоединиться к ней с ключевыми значениями, но я не был уверен, что это будет.

            WITH NonZeroTransactions AS(
    
                SELECT     TotalTRNSAMT =SUM(TRNS_AMT)OVER(PARTITION BY           
                                                              EXPENDITURES.CNTRT, 
                                                              EXPENDITURES.L_CNTRT, 
                                                              EXPENDITURES.BE, 
                                                              EXPENDITURES.L_BE, 
                                                              EXPENDITURES.SPGM_STD, 
                                                              EXPENDITURES.L_SPGM, 
                                                              EXPENDITURES.CAT, 
                                                              EXPENDITURES.L_CAT, 
                                                              EXPENDITURES.OCA, 
                                                              EXPENDITURES.L_OCA, 
                                                              EXPENDITURES.L2, 
                                                              EXPENDITURES.L1L5, 
                                                              EXPENDITURES.L_L1L5, 
                                                              EXPENDITURES.OBJ, 
                                                              EXPENDITURES.L_OBJ, 
                                                              EXPENDITURES.STFY, 
                                                              EXPENDITURES.CF
                                                           ORDER BY
                                                              EXPENDITURES.CNTRT
                                                           ),
        EXPENDITURES.CNTRT, 
        EXPENDITURES.L_CNTRT, 
        EXPENDITURES.BE, 
        EXPENDITURES.L_BE, 
        EXPENDITURES.SPGM_STD, 
        EXPENDITURES.L_SPGM, 
        EXPENDITURES.CAT, 
        EXPENDITURES.L_CAT, 
        EXPENDITURES.OCA, 
        EXPENDITURES.L_OCA, 
        EXPENDITURES.L2, 
        EXPENDITURES.L1L5, 
        EXPENDITURES.L_L1L5, 
        EXPENDITURES.OBJ, 
        EXPENDITURES.L_OBJ, 
        EXPENDITURES.STFY, 
        EXPENDITURES.CF, 
        EXPENDITURES.TRNS_AMT, 
        EXPENDITURES.MGDT, 
        EXPENDITURES.VENDOR_ID_NO, 
        EXPENDITURES.VENDOR_LONG_NAME, 
        EXPENDITURES.DESCRIPTION, 
        EXPENDITURES.INVOICE_NO, 
        EXPENDITURES.DN, 
        EXPENDITURES.OTHER_DOC_NO, 
        EXPENDITURES.PRIM_DOC_NO, 
        EXPENDITURES.SECOND_DOC_NO, 
        EXPENDITURES.TR
         FROM IDS.EXPENDITURES EXPENDITURES
         WHERE ((EXPENDITURES.BE<>'70212349')) 
        AND (EXPENDITURES.OCA LIKE '%C') 
        AND (EXPENDITURES.STFY='2017') 
        AND (EXPENDITURES.CF<>'C') 
        AND ((EXPENDITURES.MGDT<={ts '2016-07-31 00:00:00'}) AND (EXPENDITURES.MGDT>={ts '2016-07-01 00:00:00'}))
        )
    SELECT * FROM nonZeroTransactions 
    WHERE TotalTRNSAMT >0 
    
  2. select *
    from ( select <list all the columns here>
                  , sum(trns_amt) over (partition by cntrt, l_cntrt <etc.>) as tot_trns_amt
           from   ids.expenditures
           where  <all your conditions here>
         )
    where tot_trns_amt <> 0;
    

    Эта версияsum(), называемая «аналитической версией», позволяет по — прежнему отображать все строки из базовой таблицы и вычислять сумму» как если бы » вы группировали по всем этим столбцам-сумма будет одинаковой для всех строк, составляющих группу. Затем отфильтровываются строки, в которых сумма равна нулю. (Это нужно делать с аналитическими функциями — нет аналога HAVINGусловию, которое существует только для «агрегатной версии»).

    Когда вы выбираете все из только одной базовой таблицы, не нужно префиксировать имена столбцов с именем таблицы (и если вы это сделаете, дайте таблице однобуквенный псевдоним, like FROM IDS.EXPENDITURES Eи напишите столбцы likeE.CNTRT, чтобы облегчить глаз).