求帮忙精简sql语句
0
悬赏园豆：10
[已关闭问题]
关闭于 2016-01-15 15:20
下面是使用特别笨的方法构造的sql语句，希望大牛可以帮忙优化一下，不胜感激。
select 'Memory_Available_MBytes' as parameter,hostname,ROUND(avg(Memory_Available_MBytes),6) as averageValue,MAX(Memory_Available_MBytes) as maxValue, MIN(Memory_Available_MBytes) as minValue,ROUND(VAR(Memory_Available_MBytes),6) as varValue from perfermance group by hostname union all select 'Memory_Pages_sec' as parameter,hostname,ROUND(avg(Memory_Pages_sec),6) as averageValue,MAX(Memory_Pages_sec) as maxValue, MIN(Memory_Pages_sec) as minValue,ROUND(VAR(Memory_Pages_sec),6) as varValue from perfermance group by hostname union all select 'Memory_Page_Reads_sec' as parameter,hostname,ROUND(avg(Memory_Page_Reads_sec),6) as averageValue,MAX(Memory_Page_Reads_sec) as maxValue, MIN(Memory_Page_Reads_sec) as minValue,ROUND(VAR(Memory_Page_Reads_sec),6) as varValue from perfermance group by hostname
所有回答(3)
0
如果输出格式不变得滑，最简了。
0
可以在select里面使用case when then 达到你要的效果
0
已使用列转行实现了想要的效果，谢谢楼上二位。
贴出sql，供需要的人参考。
SELECT [hostname], KPI,date, value
FROM
(SELECT [hostname]
,[date]
,[Memory_Available_MBytes]
,[Memory_Pages_sec]
,[Memory_Page_Reads_sec]
FROM [dbo].[table]) p
UNPIVOT
(value FOR KPI IN
([Memory_Available_MBytes],[Memory_Pages_sec]
,[Memory_Page_Reads_sec])
)AS unpvt order by date
