GROUP BY函数取消DISTINCT
GROUP BY函数取消DISTINCT
提问于 2019-03-01 03:43:38
我有一个连接多个表的SQL查询，该查询在其中两行上生成重复项。我使用DISTINCT关键字来消除重复项：
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，我将看到重复的行
1/2/19, 5, first, 25, 1
1/5/19, 7, second, 20, 1如果我删除DISTINCT，那么这两行是重复的，得到四行：
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函数，如下所示：
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
1/2/19, 5, first, 50, 1
1/5/19, 7, second, 40, 1注意:当我在第一个查询中得到想要的结果时，您可能想知道为什么我要尝试使用GROUP BY。我只包含了正在被复制的行。由于某种原因，所有其他行都看不到这种行为。有没有办法让GROUP BY和DISTINCT一起工作？
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-03-01 03:51:06
如果要删除重复项，然后再求和值，请将查询插入到子查询中。
select day, id, name, sum(num) num, version
from (
-- your query here with DISTINCT clause
)
group by day, id, name, version如果您得到重复项，则连接条件可能存在一些问题。对我来说不难判断，不知道数据集。
Stack Overflow用户
发布于 2019-03-01 04:03:17
您可以使用SUM(DISTINCT o.num)
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54933146
复制相关文章
相似问题
腾讯云开发者
