首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >GROUP BY函数取消DISTINCT

GROUP BY函数取消DISTINCT
EN

Stack Overflow用户
提问于 2019-03-01 03:43:38
回答 2查看 50关注 0票数 1

我有一个连接多个表的SQL查询,该查询在其中两行上生成重复项。我使用DISTINCT关键字来消除重复项:

代码语言:javascript
复制
SELECT DISTINCT
          o.day as day,
          g.id AS id,
          g.name AS name,
          o.num AS num,
          o.version as version
        FROM
          table_one o
          INNER JOIN table_two t ON
            o.ID = t.ID
          INNER JOIN table_three g ON
            t.ID = g.ID
          INNER JOIN table_four gs ON
            g.ID = gs.ID
            AND
          INNER JOIN table_five s ON
            gs.ID = s.ID
          INNER JOIN table_six z ON
            s.ID = z.ID
          INNER JOIN table_seven bg ON
            bg.ID = g.ID;

这会像我想要的那样返回两行,否则如果不使用DISTINCT,我将看到重复的行

代码语言:javascript
复制
1/2/19, 5, first, 25, 1
1/5/19, 7, second, 20, 1

如果我删除DISTINCT,那么这两行是重复的,得到四行:

代码语言:javascript
复制
1/2/19, 5, first, 25, 1
1/2/19, 5, first, 25, 1
1/5/19, 7, second, 20, 1
1/5/19, 7, second, 20, 1

因此,我的最终目标是使用一个GROUP BY函数,这样我就可以添加我的o.num字段,并按其余字段对它们进行分组。如果我在上面的查询中添加一个GROUP BY函数,如下所示:

代码语言:javascript
复制
SELECT DISTINCT
          o.day as day,
          g.id AS id,
          g.name AS name,
          SUM(o.num) AS num,
          o.version as version
        FROM
          table_one o
          INNER JOIN table_two t ON
            o.ID = t.ID
          INNER JOIN table_three g ON
            t.ID = g.ID
          INNER JOIN table_four gs ON
            g.ID = gs.ID
            AND
          INNER JOIN table_five s ON
            gs.ID = s.ID
          INNER JOIN table_six z ON
            s.ID = z.ID
          INNER JOIN table_seven bg ON
            bg.ID = g.ID
        GROUP BY
          o.day as day,
          g.id AS id,
          g.name AS name,
          o.version as version;

我返回了两行,但是o.num数量翻了一番(实际上是在不使用DISTINCT的情况下执行GROUP BY

代码语言:javascript
复制
1/2/19, 5, first, 50, 1
1/5/19, 7, second, 40, 1

注意:当我在第一个查询中得到想要的结果时,您可能想知道为什么我要尝试使用GROUP BY。我只包含了正在被复制的行。由于某种原因,所有其他行都看不到这种行为。有没有办法让GROUP BYDISTINCT一起工作?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-03-01 03:51:06

如果要删除重复项,然后再求和值,请将查询插入到子查询中。

代码语言:javascript
复制
select day, id, name, sum(num) num, version
from (
  -- your query here with DISTINCT clause 
)
group by day, id, name, version

如果您得到重复项,则连接条件可能存在一些问题。对我来说不难判断,不知道数据集。

票数 4
EN

Stack Overflow用户

发布于 2019-03-01 04:03:17

您可以使用SUM(DISTINCT o.num)

代码语言:javascript
复制
SELECT o.day as day,
       g.id AS id,
       g.name AS name,
       SUM(DISTINCT o.num) AS num,
       o.version as version
FROM table_one o
INNER JOIN table_two t ON o.ID = t.ID
INNER JOIN table_three g ON t.ID = g.ID
INNER JOIN table_four gs ON g.ID = gs.ID
INNER JOIN table_five s ON gs.ID = s.ID
INNER JOIN table_six z ON s.ID = z.ID
INNER JOIN table_seven bg ON bg.ID = g.ID
GROUP BY o.day as day,
         g.id AS id,
         g.name AS name,
         o.version as version;
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/54933146

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档