在单列上分组不在所有列上 [英] Group by on single column not on all column

问题描述

Quote：

我有一个表UserType如下：

I have a table UserType as follows:

Quote：

ID	UserType
1	Admin
2	Marketing Manager
3	Hotel Manager
4	Playlist Programmer
5	Support Manager

引用：

另一个表用户如下：

And another table User as follows:

ID  Name UserType LocationID
1   TT      1         1
2   AA	    2	      1
3   BB      1         2
4   CC      2         3

引用：

我想在单列上分组UserType而不是选择列表中的所有列。

当我使用以下查询时给出错误。怎么做？

I want to group by on single column UserType and not by all the column in the select list.
When I use the following query its giving error.How to do that?

Select		usr.ID,
			usr.FirstName,
			usr.LastName,
			usr.Email,
			usr.ContactNo,
			lctn.LocName ,
			IsActive,
			CreatedBy,
			utyp.UserType
			
	 from	[User] usr,
		    Location lctn,UserType utyp
     where usr.Location_id=lctn.LocID	
     and  usr.UserType_ID=utyp.ID	
     --group by  utyp.UserType

推荐答案

根据您的问题/示例查询，您希望按单列userType进行分组。



如果您希望按一列分组，则您的选择查询只能用于一列（或者像COUNT或SUM这样的聚合函数）。



所以这个

Based on your question/sample query you are wanting to group by the single Column userType.

If you wish to group by one column your select query can only be for one column (or an aggregate function like COUNT or SUM).

So this

Select		usr.ID,
			usr.FirstName,
			usr.LastName,
			usr.Email,
			usr.ContactNo,
			lctn.LocName ,
			IsActive,
			CreatedBy,
			utyp.UserType
			
	 from	[User] usr,
		    Location lctn,UserType utyp
     where usr.Location_id=lctn.LocID	
     and  usr.UserType_ID=utyp.ID	
     --group by  utyp.UserType

需要更改为此

Would need to be changed to this

Select utyp.UserType
from [User] usr,
     Location lctn,UserType utyp
     where usr.Location_id=lctn.LocID	
     and  usr.UserType_ID=utyp.ID	
     group by  utyp.UserType

这将允许您根据我对您的问题的理解，按单列进行分组。对于您希望分组的每个列，您必须将它添加到您的分组和您的选择语句。



因此，如果您的组中有2列，那些2列应该在您的select语句中。

This will allow you to group by the single column based on how i understand your question. For every column you wish to group by you must add it to both your group by and your select statement.

So if you have 2 columns in your group by, those 2 columns should be in your select statement.

这篇关于在单列上分组不在所有列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！