使用分组时出现SQL错误：每个GROUP BY表达式都必须至少包含一个不是外部引用的列 [英] SQL error when using Group By: Each GROUP BY expression must contain at least one column that is not an outer reference

问题描述

在做我认为是最简单的查询之一时，我一直得到这个错误！我看到其他人也遇到了问题，我浏览了我见过的每个解决方案，但他们涉及更多的查询，所以我很难挑出问题。我做了一个小虚拟表来说明我的问题。

表名：grouptest

  id name 
 1 Mel 
 2 Lucy 
 3 Mandy 
 4 Mel 
 5 Mandy 
 6 Mel 
  

我想知道每个名字出现的次数，以产生如下表格：

  3 Mel 
 2 Mandy 
 1 Lucy 
  

下面是我认为应该可以工作的查询：

pre $ c $ SELECT> Count（id），Name
FROM groupbytest
GROUP BY'Name'

我得到这个错误：

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

帮助！

解决方案

您在名称字段中引用了不需要的引号。

  SELECT计数（id），
 FROM grouptest 
 GROUP BY名称
  code> 

根据您的意见，您需要 CAST 您的名称列：

  SELECT Count（id），Cast（Name as Varchar（max） ）名称
 FROM grouptest 
 GROUP BY Cast（名称为Varchar（max））
  




I've been getting this error when doing what I thought to be one of the simplest possible queries! I see other people have run into issues here too, and I've looked through every solution I've seen but they're with more involved queries so it was very hard for me to pick out the problem. I made a little dummy table to illustrate my problem.

table name: grouptest

id  name
1   Mel
2   Lucy
3   Mandy
4   Mel
5   Mandy
6   Mel

I want to find out how many times each name shows up, to produce a table like:

3 Mel
2 Mandy
1 Lucy

Here's the query I think should work:

SELECT Count(id), Name
FROM groupbytest
GROUP BY 'Name'

And I get the error:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Help!

解决方案

You have quotes around the Name field which are unneeded.

SELECT Count(id), Name
FROM grouptest
GROUP BY Name

Based on your comments, you need to CAST your Name column:

SELECT Count(id), Cast(Name as Varchar(max)) Name
FROM grouptest
GROUP BY Cast(Name as Varchar(max))

这篇关于使用分组时出现SQL错误：每个GROUP BY表达式都必须至少包含一个不是外部引用的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！