将多个select语句结果集的结果合并到表中的单个记录 [英] Combine results from multiple select statement resultsets to single record in a table

问题描述

我有一个非常特别的问题。



我需要检索EmployeeID，UserID，TotalValue的总和和COUNT个StartDate列的SUM作为单个记录来自我的存储过程。



场景：



我尝试过：



如果我试试这个：

 选择 EmployeeID，UserID，Sum（TotalValue），Sum（Count（StartDate））
 来自员工
 组 按 EmployeeID，UserID 

问题是我不能同时Sum和Count，因为它给我以下错误：



无法对包含聚合或子查询的表达式执行聚合函数

所以我做的是：

 选择 EmployeeID，UserID，TotalSummedValue ，SUM（NoOfDates） FROM  
（
 选择 EmployeeID，UserID，SUM（ TotalValue） AS  TotalSummedValue，COUNT（StartDate）
  FROM 员工
  GROUP   BY  EmployeeID，UserID 
） AS   inner  
  GROUP   BY  EmployeeID ，UserID 

但现在问题是COUNT（StartDate）返回的值受内部查询中的分组的影响。所以我基本上必须分别检索Count和其余的数据。



所以我做了一个基本查询（这是实际查询的简化版本），我在临时表中插入并重复使用的结果 - >

 插入 进入 #TempBase 
  SELECT  EmployeeID，UserID，TotalValue，StartDate  FROM 员工

并按照以下方式使用：



< pre lang =SQL> 插入 INTO ＃Temp2
选择 EmployeeID，UserID，SUM（TotalValue） AS TotalSummedValue FROM #TempBase

插入 INTO ＃Temp3
选择 SUM（NoOfDates） FROM
（
选择 COUNT（StartDate）< span class =code-keyword> AS NoOfDates FROM
#TempBase
）

然后在单个记录中输出整个内容，我目前正在做这个丑陋的事情：

 插入  INTO  #Temp4（EmployeeID，UserID，TotalSummedValue，TotalDays）
 值（（选择 EmployeeID  FROM ＃Temp2），（< span class =code-keyword>选择 UserID  FROM ＃Temp2），
（选择 TotalSummedValue  FROM ＃Temp2），（选择 NoOfDates  FROM ＃Temp3））
 
 选择 * 来自＃Temp4 

我想要知道是否有更好的方法。

解决方案

您的代码：

 选择 COUNT（StartDate） AS  NoOfDates  FROM  #Tempbase 

返回一个整数值：临时表中的行数。

所以：

 选择 SUM（NoOfDates） FROM  
（
 选择 COUNT（StartDate） AS  NoOfDates  FROM  
 #TempBase 
）

将所有值相加，并返回完全相同的值 - 一个整数，即临时表中的行数。

唯一的时间 SUM（ COUNT（x））可能有用，如果COUNT返回多个行，就像它处理GROUP BY子句一样 - 你特别说的不是你想要的 - 而且SQL不会'如果你是抱怨，因为你不会试图对包含聚合的表达式执行聚合函数，因为GROUP BY将返回行而不是聚合结果。



那你为什么要尝试使用COUNT来求和 - 除非你是GROUPing和过滤行，它不会返回任何与COUNT值本身不同的东西。

I have a very particular problem.

I need to retrieve EmployeeID, UserID, a Sum of TotalValue and SUM of COUNT of StartDate columns as a single record from my stored procedure.

Scenario :

What I have tried:

If I try this :

Select EmployeeID, UserID, Sum(TotalValue), Sum(Count(StartDate))
From Employees
Group By EmployeeID, UserID

The problem is that I cannot Sum and Count at the same time as it gives me the following error :

Cannot perform an aggregate function on an expression containing an aggregate or a subquery
So what I did was this :

Select EmployeeID, UserID, TotalSummedValue, SUM(NoOfDates) FROM
(
 Select EmployeeID, UserID, SUM(TotalValue) AS TotalSummedValue, COUNT(StartDate)
 FROM Employees
 GROUP BY EmployeeID, UserID
) AS inner
GROUP BY EmployeeID, UserID

But now the problem is that the value returned by COUNT(StartDate) is affected by the Grouping in the inner query. So I essentially have to retrieve the Count and the rest of the data separately.

So I made a base query (this is a simplified version of the actual query), results of which I insert in a temporary table and reuse ->

Insert Into #TempBase
SELECT EmployeeID, UserID, TotalValue, StartDate FROM Employees

And use it like this :

Insert INTO #Temp2
Select EmployeeID, UserID, SUM(TotalValue) AS TotalSummedValue FROM #TempBase

Insert INTO #Temp3
Select SUM(NoOfDates) FROM
(
 Select COUNT(StartDate) AS NoOfDates FROM
 #TempBase
)

And then to get the whole thing out in a single record, I am currently doing this ugly thing :

Insert INTO #Temp4 (EmployeeID, UserID, TotalSummedValue, TotalDays)
Values((Select EmployeeID FROM #Temp2), (Select UserID FROM #Temp2),
(Select TotalSummedValue FROM #Temp2),(Select NoOfDates FROM #Temp3))

Select * from #Temp4

I would like to know if there is a better way of doing this.

解决方案

Your code:

Select COUNT(StartDate) AS NoOfDates FROM #Tempbase

Returns a single integer value: the number of rows in the temporary table.
So:

Select SUM(NoOfDates) FROM
(
 Select COUNT(StartDate) AS NoOfDates FROM
 #TempBase
)

Will add up all one of the values, and return exactly the same value - a single integer which is the number of rows in your temporary table.
The only time SUM(COUNT(x)) might be of use is if the COUNT returns a number of rows, as when it processes a GROUP BY clause - which you are specifically saying is not what you want - and SQL wouldn't complain if you were, because you wouldn't be trying to "perform an aggregate function on an expression containing an aggregate" as the GROUP BY would be returning rows rather than an aggregated result.

So why are you trying to SUM the COUNT anyway - unless you are GROUPing and filtering rows, it doesn't return anything that is in any way different from the COUNT value itself.

这篇关于将多个select语句结果集的结果合并到表中的单个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！