SQL存储过程在选择列表中抛出无效错误
我正在从 mysql 转换为 sql server。
转换选择存储过程时，
CREATE PROCEDURE selPropertyByAcntID
(
@in_acntID INT
)
AS
SELECT *
, SUM(CASE
WHEN u.prop_id IS NOT NULL THEN 1
ELSE 0
END) AS UnitCount
FROM
prop_details d
INNER JOIN
acnt_property a
ON
d.prop_id = a.prop_id
LEFT JOIN
unit_details u
ON
d.prop_id = u.prop_id
WHERE
a.group_id = @in_acntID
GROUP BY
d.prop_id;
抛出以下错误：
错误：8120 严重性 16 列“prop_details.prop_title”在选择列表中无效，因为它不是聚合函数或 GROUP BY 子句中的容器。
奇怪——完全相同的存储过程在 mysql 环境中工作。
非常感谢对此的任何帮助。
I am converting from mysql to sql server.
While converting a select stored proc,
CREATE PROCEDURE selPropertyByAcntID
(
@in_acntID INT
)
AS
SELECT *
, SUM(CASE
WHEN u.prop_id IS NOT NULL THEN 1
ELSE 0
END) AS UnitCount
FROM
prop_details d
INNER JOIN
acnt_property a
ON
d.prop_id = a.prop_id
LEFT JOIN
unit_details u
ON
d.prop_id = u.prop_id
WHERE
a.group_id = @in_acntID
GROUP BY
d.prop_id;
It is throwing the following error:
Error: 8120 Severity 16
Column 'prop_details.prop_title' is invalid in the select list because it is not container in either an aggregate function or the GROUP BY clause.
strange -- the exact same stored proc isworking in a mysql environment.
Any help on this is much appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在 SQL Server 和几乎所有 RDBMS（除了 MySQL）中，您只能在
SELECT列表中包含GROUP BY列表中包含的项目或那些包裹在一个聚合体中。因此，要解决此问题，请删除
*，只需从表d和a中选择所需的列，并将此列表也添加到 <代码>分组依据In SQL Server and just about every RDBMS except MySQL you can only include in the
SELECTlist items that are included in theGROUP BYlist or those wrapped in an aggregate.So to resolve the issue get rid of
*, select only the columns you need from tablesdandaand add this list also to theGROUP BY只是一个注释，与你的问题无关。这部分：
可以简化为：
Just a note, not related to your question. This part:
can be simplified to:
对于 SQL Server（我认为事实上大多数 RDBMS...），带有 GROUP BY 的语句只能包含分组列和聚合函数。例如，这可以工作：
但这不行：
因为
ColumnC和ColumnD没有被分组。With SQL Server (I thought most RDBMS in fact...) statements with
GROUP BYcan only include the grouping columns along with aggregating functions. For example, this would work:But this would not:
Because
ColumnCandColumnDaren't being grouped.在您选择的内容中，您有
*，但仅按prop_id分组。如果你将*更改为d.prop_id你应该没问题。如果您需要任何其他列，请在选择和分组依据中指定它们。In you select you have
*, but only grouping by theprop_id. If you change you*to just bed.prop_idyou should be good. If you need any other columns specify them in the select and the group by.