SELECT CASE WHEN namerain LIKE 'T%' THEN 'T' WHEN namerain LIKE 'M%' THEN 'M' WHEN namerain LIKE 'W%' THEN 'W' END AS namerain, AVG(sax) AS avg_sax, SUM(sax) AS sum_sax FROM rain GROUP BY CASE WHEN namerain LIKE 'T%' THEN 'T' WHEN namerain LIKE 'M%' THEN 'M' WHEN namerain LIKE 'W%' THEN 'W' END;
select left(namerain,1) as A, avg(sax),sum(sax) from rain where left(namerain,1) in ('T','M','W') group by left(namerain,1)
create table t (f1 varchar(10),f2 int)insert into t select 'T21-4', 140 union all select 'T34-2', 180 union all select 'M324', 150 union all select 'M123', 170 union all select 'W78-3', 210 union all select 'W21-2', 230 select * from tselect left(f1,1) as f1,avg(f2) as f22,max(f2) as f23 from t where left(f1,1) in ('t','m','w') group by left(f1,1)f1 f22 f23 ---- ----------- ----------- M 160 170 T 160 180 W 220 230(3 行受影响)
declare @t table(namerain varchar(10),sax int) insert into @t select 'T21-4', 140 union all select 'T34-2', 180 union all select 'M324', 150 union all select 'M123', 170 union all select 'W78-3', 210 union all select 'W21-2', 230select namerain=left(namerain,1),平均值=avg(sax),总数=sum(sax) from @t group by left(namerain,1) having left(namerain,1) in ('T','W','M')namerain 平均值 总数 -------- ----------- ----------- M 160 320 T 160 320 W 220 440(3 行受影响)
看错了,是求和呀[code=SQL] select left(f1,1) as f1,avg(f2) as f22,sum(f2) as f23 from t where left(f1,1) in ('t','m','w') group by left(f1,1)f1 f22 f23 ---- ----------- ----------- M 160 320 T 160 320 W 220 440(3 行受影响) [/code
看错了,是求和呀 select left(f1,1) as f1,avg(f2) as f22,sum(f2) as f23 from t where left(f1,1) in ('t','m','w') group by left(f1,1) f1 f22 f23 ---- ----------- ----------- M 160 320 T 160 320 W 220 440 (3 行受影响)
CASE WHEN namerain LIKE 'T%' THEN 'T'
WHEN namerain LIKE 'M%' THEN 'M'
WHEN namerain LIKE 'W%' THEN 'W'
END AS namerain,
AVG(sax) AS avg_sax,
SUM(sax) AS sum_sax
FROM rain
GROUP BY
CASE WHEN namerain LIKE 'T%' THEN 'T'
WHEN namerain LIKE 'M%' THEN 'M'
WHEN namerain LIKE 'W%' THEN 'W'
END;
select left(namerain,1) as A, avg(sax),sum(sax)
from rain
where left(namerain,1) in ('T','M','W')
group by left(namerain,1)
create table t (f1 varchar(10),f2 int)insert into t
select 'T21-4', 140 union all
select 'T34-2', 180 union all
select 'M324', 150 union all
select 'M123', 170 union all
select 'W78-3', 210 union all
select 'W21-2', 230
select *
from tselect left(f1,1) as f1,avg(f2) as f22,max(f2) as f23
from t
where left(f1,1) in ('t','m','w')
group by left(f1,1)f1 f22 f23
---- ----------- -----------
M 160 170
T 160 180
W 220 230(3 行受影响)
insert into @t
select 'T21-4', 140 union all
select 'T34-2', 180 union all
select 'M324', 150 union all
select 'M123', 170 union all
select 'W78-3', 210 union all
select 'W21-2', 230select namerain=left(namerain,1),平均值=avg(sax),总数=sum(sax)
from @t
group by left(namerain,1)
having left(namerain,1) in ('T','W','M')namerain 平均值 总数
-------- ----------- -----------
M 160 320
T 160 320
W 220 440(3 行受影响)
select left(f1,1) as f1,avg(f2) as f22,sum(f2) as f23
from t
where left(f1,1) in ('t','m','w')
group by left(f1,1)f1 f22 f23
---- ----------- -----------
M 160 320
T 160 320
W 220 440(3 行受影响)
[/code
select left(f1,1) as f1,avg(f2) as f22,sum(f2) as f23
from t
where left(f1,1) in ('t','m','w')
group by left(f1,1) f1 f22 f23
---- ----------- -----------
M 160 320
T 160 320
W 220 440 (3 行受影响)