SQL查询语句，谢谢大家帮个忙，新手，非常感谢

来源：百度知道 编辑：UC知道 时间：2024/09/21 17:43:24
表是这样的，表名就叫temp
列名： dept | blackcoat | pay
A | 小明 | 2000
B | 小王 | 3000
A | 小赵 | 1600
C | 小聪 | 2800
B | 小男 | 5000
C | 小女 | 2600

问题1：每一个部门的工资总额（就是把相同的部门的工资加起来）
问题2：求出本部门的职员的工资大于本部门的平均工资

1)
SELECT temp.Dept, sum(temp.pay) as 工资总额
FROM temp
group by temp.Dept

2)select * from temp
inner join
(select temp.dept,avg(temp.pay) as pay
from temp
group by temp.dept) TA on temp.dept=TA.dept
where temp.pay>TA.pay

select dept,sum(pay) pay from temp group by dept

select a.dept,a.blackcoat,a.pay from temp a,(sleect dept,avg(pay) avg_pay from temp) b
where a.dept=b.dept and a.pay>b.avg_pay
group by a.dept,a.blackcoat,a.pay

select sum(pay) from temp
group by dept

select dept,blackcoat,pay from temp
group by dept,blackcoat,pay
having pay>(sum(pay)/count(pay) )

问题1

select dept, sum(pay)
from temp
group by dept,blackcoat;

问题2

select *
from temp
group by dept,blackcoat,pay
having pay>avg(pay);