当前位置： 文江博客话题详情

每个 GROUP BY 表达式必须至少包含一列不是外部引用

发布于 2024-10-07 23:28:19 字数 561 浏览 0 评论 0原文

我在这里做错了什么？我收到此错误：

SELECT LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), 
            PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', 
            batchinfo.datapath), 8000))-1),
            qvalues.name,
            qvalues.compound,
            qvalues.rid
FROM batchinfo JOIN qvalues ON batchinfo.rowid=qvalues.rowid
WHERE LEN(datapath)>4
GROUP BY 1,2,3
HAVING rid!=MAX(rid)

我想按具有最大删除值的第一、第二和第三列进行分组。

无需 group by 和having 即可正常工作。

原文

What am I doing wrong here? I am getting this error on:

SELECT LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), 
            PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', 
            batchinfo.datapath), 8000))-1),
            qvalues.name,
            qvalues.compound,
            qvalues.rid
FROM batchinfo JOIN qvalues ON batchinfo.rowid=qvalues.rowid
WHERE LEN(datapath)>4
GROUP BY 1,2,3
HAVING rid!=MAX(rid)

I would like to group by the first, second, and third columns having the max rid.

It works fine without the group by and having.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（9）

薄凉少年不暖心 2024-10-14 23:28:19

首先，您不能这样做：

having rid!=MAX(rid)

HAVING 子句只能包含属于聚合组属性的内容。

另外，1, 2, 3 在 SQL Server 的 GROUP BY 中无效 - 我认为这仅在 ORDER BY 中有效。

您能解释一下为什么这不是您要找的吗：

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid)
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound

To start with you can't do this:

having rid!=MAX(rid)

The HAVING clause can only contain things which are attributes of the aggregate groups.

In addition, 1, 2, 3 is not valid in GROUP BY in SQL Server - I think that's only valid in ORDER BY.

Can you explain why this isn't what you are looking for:

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid)
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound
回复 原文
梦中的蝴蝶 2024-10-14 23:28:19

好吧，正如之前所说，你不能按文字进行GROUP，我认为你很困惑，因为你可以ORDER按1、2、3。当你使用函数作为您的列，您需要按相同的表达式进行分组。此外，HAVING子句是错误的，你只能使用聚合中的内容。在这种情况下，您的查询应该如下所示：

SELECT 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid) MaxRid
FROM batchinfo join qvalues 
ON batchinfo.rowid=qvalues.rowid
WHERE LEN(datapath)>4
GROUP BY 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound

Well, as it was said before, you can't GROUP by literals, I think that you are confused cause you can ORDER by 1, 2, 3. When you use functions as your columns, you need to GROUP by the same expression. Besides, the HAVING clause is wrong, you can only use what is in the agreggations. In this case, your query should be like this:

SELECT 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid) MaxRid
FROM batchinfo join qvalues 
ON batchinfo.rowid=qvalues.rowid
WHERE LEN(datapath)>4
GROUP BY 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound
回复 原文
夏了南城 2024-10-14 23:28:19

您不能按文字分组，只能按列分组。

您可能正在寻找这样的内容：

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1) as pathinfo,
qvalues.name,
qvalues.compound,
qvalues.rid
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by pathinfo, qvalues.name, qvalues.compound
having rid!=MAX(rid)

首先，您必须为第一个表达式指定一个带有 as 的列名称。然后，您必须在 group by 表达式中指定列的名称。

You can't group by literals, only columns.

You are probably looking for something like this:

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1) as pathinfo,
qvalues.name,
qvalues.compound,
qvalues.rid
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by pathinfo, qvalues.name, qvalues.compound
having rid!=MAX(rid)

First of all, you have to give that first expression a column name with as. Then you have to specify the names of the columns in the group by expression.

回复 原文
盗梦空间 2024-10-14 23:28:19

当我在 SELECT 中有一个“固定”值列时，我收到了此错误消息，并且作为一个好孩子，我也在 GROUP BY 中使用了它。

例如：

SELECT ID, SUM(Quantity), 'Person'
FROM Persons
GROUP BY ID, 'Person'

修复：

SELECT ID, SUM(Quantity), 'Person'
FROM Persons
GROUP BY ID

固定的 'Person' 值毫无意义，因为它在每一行中都是相同的值，并且没有任何内容可以与该值分组。或者这是我对错误的解释。

我使用这个固定值列的原因是因为我正在准备查询以在数据透视表中使用它。

I had this error message when i had a "fixed" value column in SELECT and as a good boy, i used it in the GROUP BY too.

For example:

SELECT ID, SUM(Quantity), 'Person'
FROM Persons
GROUP BY ID, 'Person'

the fix:

SELECT ID, SUM(Quantity), 'Person'
FROM Persons
GROUP BY ID

The fixed 'Person' value is pointless since it's the same value throughout every row and there is nothing to group by with this value. Or this is my interpretation of the error.

The reason why i use this fixed value column, is because i was preparing queries to use it in a Pivot table.

回复 原文
余生共白头 2024-10-14 23:28:19

我认为您没有正确使用 GROUP BY 。

GROUP BY 的要点是在执行数学/聚合函数之前根据特定列将表组织为多个部分。

例如，在此表中：

Name    Age   Salary
Bob     25     20000
Sally   42     40000
John    42     90000

SELECT 语句可以按姓名（Bob、Sally 和 John 各自为单独的组）、年龄（Bob 为一组，Sally 和 John 为另一组）或薪水（几乎相同）进行 GROUP BY结果作为名称）。

按“1”分组没有任何意义，因为“1”不是列名。

I think you're not using GROUP BY properly.

The point of GROUP BY is to organize your table into sections based off a certain column or columns before performing math/aggregate functions.

For example, in this table:

Name    Age   Salary
Bob     25     20000
Sally   42     40000
John    42     90000

A SELECT statement could GROUP BY name (Bob, Sally, and John would each be separate groups), Age (Bob would be one group, Sally and John would be another), or Salary (pretty much same result as name).

Grouping by "1" doesn't make any sense because "1" is not a column name.

回复 原文
著墨染雨君画夕 2024-10-14 23:28:19

我刚刚在选择查询的 group by 子句中使用 GETDATE() [即外部引用] 时发现了此错误。

当将其替换为相应表中的日期列时，它会被清除。

认为作为一个简单的例子来分享。干杯；）

I just found this error., while using GETDATE() [i.e outer reference] in the group by clause in a select query.

When replaced it with date column from the respective table it cleared.

Thought to share as a simple example. cheers ;)

回复 原文
成熟的代价 2024-10-14 23:28:19

当您使用GROUP BY时，您还需要对不在group by子句内的列使用聚合函数。

我不知道你到底想做什么，但我想这会起作用：

select 
    LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000),
    PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
    qvalues.name,
    qvalues.compound,
    MAX(qvalues.rid)
from
    batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where
    LEN(datapath)>4
group by
    LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000),
    PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
    qvalues.name,
    qvalues.compound
having
    rid!=MAX(rid)

编辑：
我在这里尝试做的是一个 group by ，其中包含除 rid 之外的所有字段。如果这不是您想要的，为了获得有效的 SQL 语句，您需要做的是为每个删除的 group by 字段添加聚合函数调用...

When you're using GROUP BY, you need to also use aggregate functions for the columns not inside your group by clause.

I don't know exactly what you're trying to do, but I guess this would work:

select 
    LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000),
    PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
    qvalues.name,
    qvalues.compound,
    MAX(qvalues.rid)
from
    batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where
    LEN(datapath)>4
group by
    LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000),
    PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
    qvalues.name,
    qvalues.compound
having
    rid!=MAX(rid)

Edit:
What I'm trying to do here is a group by with all fields but rid. If that's not what you want, what you need to do in order to have a valid SQL statement is adding an aggregate function call for each removed group by field...

回复 原文
另类 2024-10-14 23:28:19

下面是一个简单的查询，用于查找药品类型为 A 且产量超过 2 的公司名称。

SELECT CNAME 
FROM COMPANY 
WHERE CNO IN (
    SELECT CNO 
    FROM MEDICINE 
    WHERE type='A' 
    GROUP BY CNO HAVING COUNT(type) > 2
)

Here's a simple query to find company name who has a medicine type of A and makes more than 2.

SELECT CNAME 
FROM COMPANY 
WHERE CNO IN (
    SELECT CNO 
    FROM MEDICINE 
    WHERE type='A' 
    GROUP BY CNO HAVING COUNT(type) > 2
)
回复 原文
淡紫姑娘！ 2024-10-14 23:28:19

我因为一个愚蠢的错误而收到此错误 - 我的 GROUP BY 列用引号引起来！

错误

GROUP BY 'business'

正确

GROUP BY business

信用：@SOound的答案让我想到了这一点出去

I got this error because of a silly mistake - I had my GROUP BY column in quotes!

Wrong

GROUP BY 'business'

Correct

GROUP BY business

Credit: @SOund's answer led me to figure this out

回复 原文
~没有更多了~

关于作者

成熟的代价

暂无简介

0 文章
0 评论
373 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lioqio

文章 0 评论 0

Single

文章 0 评论 0

禾厶谷欠

文章 0 评论 0

alipaysp_2zg8elfGgC

文章 0 评论 0

qq_N6d4X7

文章 0 评论 0

放低过去

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文