T-SQL GROUP BY：包含其他分组列的最佳方法 [英] T-SQL GROUP BY: Best way to include other grouped columns

问题描述

我正在加入一些表格，并将一些表格集合在一起通过GROUP BY列。

一个简单的例子是员工和项目：

 从雇员中选择empID，fname，lname，title，dept，count（projectID）
 E在E.empID = P.projLeader 
中加入项目P 
  

...可以在MySQL中使用，但MS SQL更严格，要求所有内容都包含在聚合函数中或是GROUP BY子句的一部分。

当然，在这个简单的例子中，我假设我可以在group by子句中包含额外的列。但是我处理的实际查询非常复杂，并且包含了一些在非聚合列上执行的操作......也就是说，如果试图将它们全部包含在group by子句中，它会变得很难看。

那么有没有更好的方法来做到这一点？

解决方案

你可以用它来处理这些问题：

  select e.empID，fname，lname，title，dept， projectIDCount 
 from 
（
）从雇员中选择empID，count（projectID）作为projectIDCount 
 E在E.empID = P.projLeader 
上加入项目P通过empID 
）idList 
内部加入员工e on idList.empID = e.empID 
  

通过这种方式，您可以通过操作避免多余的组，并且可以获取任何您想要的数据。此外，您还有更好的机会在某些场景中良好地使用索引（如果您没有返回完整的信息），并且可以更好地与分页组合。

I'm a MySQL user who is trying to port some things over to MS SQL Server.

I'm joining a couple of tables, and aggregating some of the columns via GROUP BY.

A simple example would be employees and projects:

select empID, fname, lname, title, dept, count(projectID)
from employees E left join projects P on E.empID = P.projLeader
group by empID

...that would work in MySQL, but MS SQL is stricter and requires that everything is either enclosed in an aggregate function or is part of the GROUP BY clause.

So, of course, in this simple example, I assume I could just include the extra columns in the group by clause. But the actual query I'm dealing with is pretty complicated, and includes a bunch of operations performed on some of the non-aggregated columns... i.e., it would get REALLY ugly to try to include all of them in the group by clause.

So is there a better way to do this?

解决方案

You can get it to work with something around these lines:

select e.empID, fname, lname, title, dept, projectIDCount
from
(
   select empID, count(projectID) as projectIDCount
   from employees E left join projects P on E.empID = P.projLeader
   group by empID
) idList
inner join employees e on idList.empID = e.empID

This way you avoid the extra group by operations, and you can get any data you want. Also you have a better chance to make good use of indexes on some scenarios (if you are not returning the full info), and can be better combined with paging.

这篇关于T-SQL GROUP BY：包含其他分组列的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！