sql查询，以获取各列中每个元素的总和

j2qf4p5b 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(330)

几天前我曾问过这个问题，但解决方案并不完美，因此我用新的逻辑问这个问题-

SELECT   person_number,taxable_earn, Basic_Life,Tax_Units
      FROM   (SELECT     ppa.effective_date, 
                         pra.result_value , 
                         pra.elementname, 
                         prd.person_number 
                FROM     pay_pay_relationships_dn prd 
               INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id 
               INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id 
               INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id 
               WHERE ppa.payroll_id = pap.payroll_id
                 AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
     PIVOT    (SUM(result_value )
               FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units))

上面的查询给我的输出是-

EMPLOYEENUMBER           taxable_earn  Basic_Life     Tax_Units
           10                         6.7                           
           10                                       7.8             
           10                                                       9.2

我希望输出像-

EMPLOYEENUMBER         taxable_earn  Basic_Life     Tax_Units
       10                       6.7         7.8             9.2

如何调整上述查询以获得此输出？

sql oracle oracle-sqldeveloper

来源：https://stackoverflow.com/questions/63094575/sql-query-to-get-sum-of-each-element-in-seperate-columns

3条答案

按热度按时间

gopyfrb31#

把它拿走 ppa.effective_date, 从嵌套子查询：

SELECT   person_number,taxable_earn, Basic_Life,Tax_Units
      FROM   (SELECT     
                         pra.result_value , 
                         pra.elementname, 
                         prd.person_number 
                FROM     pay_pay_relationships_dn prd 
               INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id 
               INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id 
               INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id 
               WHERE ppa.payroll_id = pap.payroll_id
                 AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
     PIVOT    (SUM(result_value )
               FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units))

赞(0）回复(0）举报 2021-07-29

4xy9mtcn2#

在最外层的select和group by的每一列上使用max函数，并将当前子select保持原样：

select person
     , max(taxable_earn) taxable_earn
     , max(basic_life)   basic_life
     , max(tax_units)    tax_units 
  from ( <your current sub select as is > )
 group by person;

赞(0）回复(0）举报 2021-07-29

g0czyy6m3#

当有公共值并且您想在一个地方收集它们时，请尝试使用“groupby”子句

赞(0）回复(0）举报 2021-07-29