计算组结果并将其与细节合并 [英] 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
toValue3
. For simplicity considerAVG
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屋!