计算组结果并将其与细节合并 [英] Calculate group result and merge it with details

问题描述

请考虑这个结果（报告）：

 状态值1值2值3 
 ------- -------------------------------------- 
 State1 103 23％3 
 State2 105 32％12 
 State3 150 2％23 
 Group1 120 19％3 
 =================== 
 State4 200 40％5 
 State5 250 2％12 
 Group2 225 21％8 
 =================== 
 ... 
  

我有组，每组包含一些状态。不，我想对我的详细信息数据运行查询，并对每个组运行一些自定义函数以获取 Value1 至 Value3 。为简单起见，请考虑 AVG 函数。

我使用 Group By 子句来生成状态结果，但我怎样才能将我的结果与状态组合？

谢谢

>编辑1）

这是基本的数据，这可能与上面的输出不匹配：

  Id StateName Value1 Value2 Value3 
 1 State1 1 2 3 
 2 State2 4 2 2 
 3 State2 3 3 8 
 4 State2 3 8 5 
 ... 
  

---

>编辑2）

例如：

  State1，State2，State3属于`Group1` 
 State4，state5属于`Group2` 
 ... 
  

解决方案

我认为你看起来像下面，我用 ROLLUP 函数来实现存档功能。

  DECLARE @tblStates AS Table 
（
 GroupName VARCHAR（10），
 StateName VARCHAR（50），
 Value1 INT，
 Value2 INT，
 Value3 INT 
）
 
 INSERT INTO @tblStates VALUES（'Group1'，'State1'，103,23,3 ）
 INSERT INTO @tblStates VALUES（'Group1'，'State2'，105,32,12）
 INSERT INTO @tblStates VALUES（'Group1'，'State3'，150,2,23）
 INSERT INTO @tblStates VALUES（'Group2'，'State3'，50,10,8）
 INSERT INTO @tblStates VALUES（'Group2'，'State4'，80,22,1）
 INSERT INTO @tblStates VALUES（'Group2'，'State5'，20,18,45）
 
; WITH T as 
（
 SELECT 
 GroupName，
 StateName，
 AVG（Value1）AS Value1，
 AVG（Value2）AS Value2，
 MIN（Value3）AS Value3 
 FROM @tblStates 
 GROUP BY ROLLUP（GroupName，StateName）
）
 SELECT 
 CASE ISNULL（StateName，''）WHEN''然后GroupName ELSE StateName END AS StateName，
 Value1，
 Value2，
 Value3 
 FROM T 
 WHERE 
 T.GroupName IS NOT NULL 
 ORDER BY GroupName 
   
 
 
 
    
 
Please consider this result (Report):
State       Value1       Value2        Value3
---------------------------------------------
State1        103         23%            3
State2        105         32%            12
State3        150         2%             23
Group1        120         19%            3
===================
State4        200         40%            5
State5        250         2%             12
Group2        225         21%            8
===================
...
I have groups and each group contains some states. No I want to run a query on my details data and run some custom function on each group to obtain Value1 to Value3. For simplicity consider AVG function.

I'm using Group By clause to generate states result but how I can merge group of states with my result?

Thanks

Edit 1)

this is the basic Data and this may not match above output:
Id      StateName      Value1       Value2      Value3
1       State1           1             2          3
2       State2           4             2          2
3       State2           3             3          8
4       State2           3             8          5
...




Edit 2)

For Example :
State1, State2, State3 Belong to `Group1`
State4, state5 Belong to `Group2`
...

解决方案
I think you are looking as below, I have used ROLLUP function to achive functionality.
DECLARE @tblStates AS Table
(
    GroupName VARCHAR(10),
    StateName VARCHAR(50),
    Value1 INT,
    Value2 INT,
    Value3 INT
)   

INSERT INTO @tblStates VALUES('Group1','State1',103,23,3)
INSERT INTO @tblStates VALUES('Group1','State2',105,32,12)
INSERT INTO @tblStates VALUES('Group1','State3',150,2,23)
INSERT INTO @tblStates VALUES('Group2','State3',50,10,8)
INSERT INTO @tblStates VALUES('Group2','State4',80,22,1)
INSERT INTO @tblStates VALUES('Group2','State5',20,18,45)

;WITH T as
(
    SELECT
        GroupName,
        StateName,
        AVG(Value1) AS Value1,
        AVG(Value2) AS Value2,
        MIN(Value3) AS Value3
    FROM @tblStates
    GROUP BY ROLLUP(GroupName,StateName)        
)
SELECT 
    CASE ISNULL(StateName,'') WHEN '' THEN GroupName ELSE StateName END AS StateName,
    Value1,
    Value2,
    Value3      
FROM T  
WHERE 
T.GroupName IS NOT NULL 
ORDER BY GroupName
Output:



                        
这篇关于计算组结果并将其与细节合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！