sql语句请教

[lcyhjx]

sql语句请教

有一个员工的薪水表，字段如下：
EmployeeSalary(Table)
EName , Age, Salary (Filed)
现在我想用一条sql语句得到如下数据 Age < 30的平均薪水， 30 <= Age <= 35 的平均薪水 , Age > 35的平均薪水
示例数据
EName Age Salary
a 32 4000
b 25 3000
c 27 3600
e 22 3000
f 37 5000
g 34 4200
h 36 4500

得到如下的三行数据
<30 3200
30 -35 4100
>35 4750

请问使用一条sql语句怎么实现?

[dawugui]

select item,avg(Salary)
from
(
select 
  case when Age < 30 then '<30'
       when age between 30 and 35 then '30-35'
       when age > 35 then '35'
  end item,
  *
from tb
) t
group by item

[百年树人]

select 
  '<30' as age,
  (select avg(salary) from EmployeeSalary where age<30)
union all
select 
  '30-35',
  (select avg(salary) from EmployeeSalary where age between 30 and 35)
union all
select 
  '>35',
  (select avg(salary) from EmployeeSalary where age>35)

[dawugui]

create table tb(EName varchar(10), Age int,Salary int)
insert into tb values('a' ,       32 ,       4000 )
insert into tb values('b' ,       25 ,       3000 )
insert into tb values('c' ,       27 ,       3600 )
insert into tb values('e' ,       22 ,       3000 )
insert into tb values('f' ,       37 ,       5000 )
insert into tb values('g' ,       34 ,       4200 )
insert into tb values('h' ,       36 ,       4500 )
go

select item,avg(Salary)
from
(
select 
  case when Age < 30 then '<30'
       when age between 30 and 35 then '30-35'
       when age > 35 then '>35'
  end item,
  *
from tb
) t
group by item

drop table tb

/*
item              
----- ----------- 
<30   3200
>35   4750
30-35 4100

（所影响的行数为 3 行）
*/

[chuifengde]

DECLARE @a TABLE(NAME CHAR(1),age INT,salary INT)
INSERT @a SELECT 'a',        32,        4000 
union all select 'b',        25,        3000 
union all select 'c',        27,        3600 
union all select 'e',        22,        3000 
union all select 'f',        37,        5000 
union all select 'g',        34,        4200 
union all select 'h',        36,        4500

select case when age<30 then '<30'
     when age between 30 and 35 then '30-35'
     else '>35' 
END 年龄 
,SUM(salary) 平均薪水
from @a
group by
case when age<30 then '<30'
     when age between 30 and 35 then '30-35'
     else '>35' 
end

--result
/*年龄    平均薪水        
----- ----------- 
<30   9600
>35   9500
30-35 8200

（所影响的行数为 3 行）

*/

[百年树人]

---测试数据---
if object_id('[EmployeeSalary]') is not null drop table [EmployeeSalary]
go
create table [EmployeeSalary]([EName] varchar(1),[Age] int,[Salary] int)
insert [EmployeeSalary]
select 'a',32,4000 union all
select 'b',25,3000 union all
select 'c',27,3600 union all
select 'e',22,3000 union all
select 'f',37,5000 union all
select 'g',34,4200 union all
select 'h',36,4500
 
---查询---
select 
  '<30' as age,
  (select avg(salary) from EmployeeSalary where age<30) as Salary
union all
select 
  '30-35',
  (select avg(salary) from EmployeeSalary where age between 30 and 35)
union all
select 
  '>35',
  (select avg(salary) from EmployeeSalary where age>35)


---结果---
age   Salary      
----- ----------- 
<30   3200
30-35 4100
>35   4750

（所影响的行数为 3 行）

[liangCK]

SELECT
    CASE WHEN age<30 THEN 'age<30'
         WHEN age BETWEEN 30 AND 35 THEN '30-35'
         ELSE '>35' END as AGE,
    AVG(Salary) AS Salary
FROM tb
GROUP BY 
    CASE WHEN age<30 THEN 'age<30'
         WHEN age BETWEEN 30 AND 35 THEN '30-35'
         ELSE '>35' END

[chuifengde]

哦,平均用avg

[interfacejava]

只能顶 你们速度真快啊

[hcw_peter]

LZ看看下面的SQL是否是你想要的？？
-------------------------------------------------
select '< 30' as Age,
avg(Salary) as 'AVG Salary'
from EmployeeSalary
where Age < 30
union all
select '30 - 35' as Age,
avg(Salary) as 'AVG Salary'
from EmployeeSalary
where Age between 30 and 35
union all
select '> 35' as Age,
avg(Salary) as 'AVG Salary'
from EmployeeSalary
where Age > 35
-------------------------------------------------

[lcyhjx]

感谢各位，使用group by & cash.. when .then end 是我想要的。union的方式也可以实现，但是不是我想要的饿唧结果，不过也同样感谢各位的回答。结帖了