时间戳

首页 > 解决方案 > SQL Server：Group By 导致“列无效”错误，如何解决？

sql - SQL Server：Group By 导致“列无效”错误，如何解决？

问题描述

我正在尝试根据上次更新的机会（使用：）过滤cg_group名称（请检查查询）和分组（使用：）结果。GROUP BYORDER BY opportunities.date_modified DESC

当我使用不使用 group by 的查询时，它返回以下结果：

SELECT cg_groups.name
FROM cg_groups
JOIN cg_groups_cstm ON cg_groups_cstm.id_c = cg_groups.id
JOIN accounts_cstm ON cg_groups.name = accounts_cstm.client_group_c
JOIN accounts ON accounts.id = accounts_cstm.id_c
JOIN accounts_opportunities ON accounts.id = accounts_opportunities.account_id
JOIN opportunities ON accounts_opportunities.opportunity_id = opportunities.id
WHERE cg_groups.deleted='0' AND cg_groups_cstm.status_c='1' AND opportunities.deleted='0' 
ORDER BY opportunities.date_modified DESC

结果：

ABC Group
ABC Group
CBC Group
ABC Group
XYZ Group

但我想按以下顺序分组：

ABC Group
CBC Group
XYZ Group

为此，我添加了GROUP BY cg_groups.name

SELECT cg_groups.name
FROM cg_groups
JOIN cg_groups_cstm ON cg_groups_cstm.id_c = cg_groups.id
JOIN accounts_cstm ON cg_groups.name = accounts_cstm.client_group_c
JOIN accounts ON accounts.id = accounts_cstm.id_c
JOIN accounts_opportunities ON accounts.id = accounts_opportunities.account_id
JOIN opportunities ON accounts_opportunities.opportunity_id = opportunities.id
WHERE cg_groups.deleted='0' AND cg_groups_cstm.status_c='1' AND opportunities.deleted='0' 
GROUP BY cg_groups.name
ORDER BY opportunities.date_modified DESC

但现在我得到这个错误：

消息 8127，级别 16，状态 1，第 10
行列“opportunities.date_modified”在 ORDER BY 子句中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

有人请帮我解决这个问题，谢谢。

标签： sqlsql-server

解决方案

distinct在你的SELECT陈述之后添加怎么样。

Select distinct ... from ...

推荐阅读