如何包含其他分组列 [英] How to include other grouped columns

问题描述

我正在加入几个表，并通过 GROUP BY 聚合一些列.这就是我对 MySQL 所做的，现在我将它们移植到 MSSQL.

I'm joining a couple of tables, and aggregating some of the columns via GROUP BY. This is what I've done with MySQL, now I'm porting them to MSSQL.

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

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

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

...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.

当然，在这个简单的例子中，我假设我可以在 group by 子句中包含额外的列.但是我正在处理的实际查询非常复杂，并且包括对一些非聚合列执行的一系列操作......即，尝试将所有这些列都包含在 group by 子句中会变得非常丑陋.

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

这样你就可以避免额外的 group by 操作，你可以得到你想要的任何数据.此外，您有更好的机会在某些情况下充分利用索引(如果您没有返回完整信息)，并且可以更好地与分页结合使用.

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.

这篇关于如何包含其他分组列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！