如何从mysql表中求出重复值 [英] How to sum the duplicate values from mysql table

问题描述

我想从下表中检索有关日期和ref_nr列的重复美元金额总和

i wanted to retrieve the sum of duplicate USD amount from below table with respect to date and ref_nr columns

uid         date       USD      Ref_Nr
1     2018-04-11    1       7 
1     2018-04-11    2       7
1     2018-04-11    3       8
1     2018-04-11    4       8
1     2018-04-11    6       6
1     2018-04-11    6       6 
1     2018-04-10    3       7
1     2018-04-10    5       7
1     2018-04-10    2       8
1     2018-04-10    2       8

这是我的SQL查询以及我尝试过的操作，但是我没有得到正确的输出，请帮助我

Here is my sql query and what i tried, but iam not getting proper output, please help me

SELECT  uid 
     ,  date 
     , SUM(USD) AS USD 
     ,  Ref_Nr  
  FROM my_table
 GROUP 
    BY `date`;

此处为预期输出

uid         date       USD      Ref_Nr 
1     2018-04-11    3       7  
1     2018-04-11    7       8 
1     2018-04-11    12      6 
1     2018-04-10    8       7 
1     2018-04-10    4       8

推荐答案

SELECT  uid 
     ,  date 
     , SUM(USD) AS USD 
     ,  Ref_Nr  
  FROM my_table
 GROUP 
    BY `date`, Ref_Nr, uid;

在这种情况下，您必须在分组依据中具有Ref_Nr才能获得所需的结果.不需要基于示例数据的uid；但明智的做法是始终按分组依据中的选择对未汇总的字段进行分组.这在mySQL中起作用的唯一原因是因为它们扩展了分组；其他大多数RDBMS都会引发有关分组依据中缺少的非聚合字段的错误.在版本5.7.5和更高版本中，默认情况下禁用此功能，而默认情况下则启用此功能.

In this case you must have Ref_Nr in the group by to get the desired results. uid, based on sample data, isn't needed; but it is wise to always group by the non-aggregated fields from the select in the group by. The only reason this works in mySQL is because they extend the group by; most other RDBMS would throw an error about the missing non-aggregated fields in the group by. In version 5.7.5 and higher this feature is disabled by default where enabled by default prior.

关于为什么在该组中需要ref_nr的原因:

mySQL引擎认为您只想按日期分组.因此，所有ref_NR都加在一起，系统仅按日期选择一个即可显示； uid也一样；但由于它们都是相同的；你不在乎ref_nr就是这种情况.

The mySQL engine believes you want to just group by date. So all the ref_NR's get summed together and the system simply picks one per date to display; same for uid; but since they are all the same; you don't care. This in'st the case with the ref_nr.

因此，要解决此问题，只需将ref_nr添加到组中，并添加UID无效.因此，最好将选择中的所有非聚合列按分组进行分组.

So to resolve the issue, just add ref_nr to the group by and out of good from add UID. So it is good from to group by all non-aggregated columns from the select into the group by.

这篇关于如何从mysql表中求出重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！