我想将“表1”中的最大值、最小值、平均值、记录数、标准差查询出来后放入一个表中,该怎么做那?
ID values
1 20
2 10
3 15
4 790
5 2.3
像这样按顺序存入就可以了,当然
这样更好:MAX MIN AVG COUNT STDEV
20 10 15 790 2.3
ID values
1 20
2 10
3 15
4 790
5 2.3
像这样按顺序存入就可以了,当然
这样更好:MAX MIN AVG COUNT STDEV
20 10 15 790 2.3
declare @t table (ID int,[values] numeric(6,2))
insert into @t
select 1,20 union all
select 2,10 union all
select 3,15 union all
select 4,790 union all
select 5,2.3select * from @t
/*
ID values
----------- ---------------------------------------
1 20.00
2 10.00
3 15.00
4 790.00
5 2.30
*/select
max(case when ID=1 then [values] else 0 end) as [MAX],
max(case when ID=2 then [values] else 0 end) as [MIN] ,
max(case when ID=3 then [values] else 0 end) as [AVG] ,
max(case when ID=4 then [values] else 0 end) as [COUNT] ,
max(case when ID=5 then [values] else 0 end) as [STDEV]
--into newtablename
from @t
declare @t table (ID int,[values] numeric(6,2))
insert into @t
select 1,20 union all
select 2,10 union all
select 3,15 union all
select 4,790 union all
select 5,2.3select * from @t
/*
ID values
----------- ---------------------------------------
1 20.00
2 10.00
3 15.00
4 790.00
5 2.30
*/
--第一种结果
select
max(case when ID=1 then [values] else 0 end) as [MAX],
max(case when ID=2 then [values] else 0 end) as [MIN] ,
max(case when ID=3 then [values] else 0 end) as [AVG] ,
max(case when ID=4 then [values] else 0 end) as [COUNT] ,
max(case when ID=5 then [values] else 0 end) as [STDEV]
--into newtablename
from @t
/*
MAX MIN AVG COUNT STDEV
------- ------- -------- --------- ----------
20.00 10.00 15.00 790.00 2.30
*/--第二种结果
select
max([values]) as [MAX],
min([values]) as [MIN] ,
avg([values]) as [AVG] ,
count([values]) as [COUNT] ,
STDEV([values]) as [STDEV]
--into newtablename
from @t
/*
MAX MIN AVG COUNT STDEV
-------- ------- ----------- -------- ----------------------
790.00 2.30 167.460000 5 348.071843158851
*/
select max(Col),min(Col),avg(Col),count(*) from T1
insert into @t
select 1,20 union all
select 2,10 union all
select 3,15 union all
select 4,790 union all
select 5,2.3select * from @tselect max([values])as max,min([values])as min,avg([values])as avg, count([values])as count,stdev([values])as stdev from @t