我如何使用SQL Server获得此结果 [英] How can i get this result using sql server

问题描述

这是我的桌子...
从表1中选择*


-------------------------------------------------- ------------------------------
名称v1 v2 v3
-------------------------------------------------- ------------------------------
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
-------------------------------------------------- ------------------------------

我需要这个结果
-------------------------------------------------- ------------------------------
名称v1 v2 v3
-------------------------------------------------- ------------------------------
平均1.75 3.65 2.14(此新行)
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
-------------------------------------------------- ------------------------------

行Avg为v1值(1.2、2.1、1.0、2.7)为1.75
v2值(1.2,2.4,8.5,2.5)为3.65
v3值(2.0,3.4,0.56,2.6)为2.14


行avg使用avg()动态添加..


请有人帮我...

This is my table...
select * from table1


--------------------------------------------------------------------------------
name v1 v2 v3
--------------------------------------------------------------------------------
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
--------------------------------------------------------------------------------

I need this result
--------------------------------------------------------------------------------
name v1 v2 v3
--------------------------------------------------------------------------------
avg 1.75 3.65 2.14(this new row)
a 1.2 1.2 2.0
b 2.1 2.4 3.4
c 1.0 8.5 0.56
d 2.7 2.5 2.6
--------------------------------------------------------------------------------

The row Avg is v1 values(1.2,2.1,1.0,2.7) is 1.75
v2 values (1.2,2.4,8.5,2.5) is 3.65
v3 values (2.0,3.4,0.56,2.6) is 2.14


the row avg is added dynamicly using avg()..


please anyone help me...

推荐答案

也许是这样的:

Perhaps something like:

select name, avg(v1),avg(v2),avg(v3)
from yourtable
group by name
with rollup

您可以使用Union运算符将新行添加到结果集中.
例如

You can use Union operator to add new row to result set.
e.g.

SELECT 'AVERAGE',AVG(V1),AVG(V2),AVG(V3) FROM TABLENAME
UNION
SELECT NAME,V1,V2,V3 FROM TABLENAME
ORDER BY 1

下面的查询可能会对您有所帮助..

below Query may help you..

select name,v1 from(
select 'Avg' as name,AVG(V1) as v1 from temp
union select name,v1 from temp)  as ss

添加v2，v3列...

Add v2,v3 columns ...

这篇关于我如何使用SQL Server获得此结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！