sql—使用mysql根据另一列中的不同值聚合一列时排除重复行

h6my8fg2 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(373)

给定表employees，我需要编写一个查询来获取每个部门的总工资。表中任何重复的e\u id都不能计数两次（在本例中是molly）。
表如下所示：
（请原谅我插入数据表的方式，我不太习惯在上面发问）

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}
.tg .tg-0lax{text-align:left;vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-0pky">e_id</th>
    <th class="tg-0lax">l_name</th>
    <th class="tg-0lax">f_name</th>
    <th class="tg-0lax">salary</th>
    <th class="tg-0lax">dept_id</th>
  </tr>
  <tr>
    <td class="tg-0lax">12</td>
    <td class="tg-0lax">grace</td>
    <td class="tg-0lax">Barbara</td>
    <td class="tg-0lax">8400</td>
    <td class="tg-0lax">1</td>
  </tr>
  <tr>
    <td class="tg-0lax">34</td>
    <td class="tg-0lax">Gates</td>
    <td class="tg-0lax">David</td>
    <td class="tg-0lax">8000</td>
    <td class="tg-0lax">1</td>
  </tr>
  <tr>
    <td class="tg-0lax">39</td>
    <td class="tg-0lax">Patrick</td>
    <td class="tg-0lax">Neil</td>
    <td class="tg-0lax">7500</td>
    <td class="tg-0lax">1</td>
  </tr>
  <tr>
    <td class="tg-0lax">45</td>
    <td class="tg-0lax">Park</td>
    <td class="tg-0lax">Molly</td>
    <td class="tg-0lax">8100</td>
    <td class="tg-0lax">2</td>
  </tr>
  <tr>
    <td class="tg-0lax">45</td>
    <td class="tg-0lax">Park</td>
    <td class="tg-0lax">Molly</td>
    <td class="tg-0lax">8100</td>
    <td class="tg-0lax">2</td>
  </tr>
  <tr>
    <td class="tg-0lax">75</td>
    <td class="tg-0lax">Gates</td>
    <td class="tg-0lax">Ryan</td>
    <td class="tg-0lax">8300</td>
    <td class="tg-0lax">2</td>
  </tr>
</table>

所需输出为：

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-s268{text-align:left}
.tg .tg-0lax{text-align:left;vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-s268">Uniq_Dept</th>
    <th class="tg-s268">TotalSalary</th>
  </tr>
  <tr>
    <td class="tg-s268">1</td>
    <td class="tg-s268">23900</td>
  </tr>
  <tr>
    <td class="tg-0lax">2</td>
    <td class="tg-0lax">16400</td>
  </tr>
</table>

sql mysql duplicates Distinct

来源：https://stackoverflow.com/questions/52582464/exclude-duplicate-rows-while-aggregating-one-column-based-on-distinct-values-in

1条答案

按热度按时间

kx1ctssn1#

你可以试着用 distinct 在子查询中按行删除重复项 dept_id 那就做吧 SUM 查询#1

SELECT dept_id as Uniq_Dept,SUM(salary) as TotalSalary 
FROM (
  SELECT distinct dept_id, salary
  FROM t 
) t1
GROUP BY dept_id;

| Uniq_Dept | TotalSalary |
| --------- | ----------- |
| 1         | 23900       |
| 2         | 16400       |

db fiddle视图

赞(0）回复(0）举报 2021-06-20

我来回答

sql—使用mysql根据另一列中的不同值聚合一列时排除重复行

1条答案

相关问题

热门标签

最新问答