sql多表分组查询 group by 表别名
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Sql多表分组查询
表:UserSalary
1.显示最高工资人员信息:
sql="select UserName,UserSex,Department,Salary ,Date from [UserSalary] where Salary=(select Max(Salary) from UserSalary)"
response.Write("
2.显示每个部门最高工资的员工信息
sql="select a.* from UserSalary a,(select Department,max(Salary) as Salary from UserSalary group by Department) b where a.Department = b.Department and a.Salary = b.Salary"
3.显示每个部门最高工资的员工信息,同时要求大于2500的才显示(Salary having Salary > 2500).
sql="select a.* from UserSalary a,(select Department,max(Salary) as Salary from UserSalary group by Department,Salary having Salary > 2500) b where a.Department = b.Department and a.Salary = b.Salary"
4.分组统计,统计每个部门员工数,总工资,平均工资等.
sql="select Department ,count(id) as PepCount, sum(Salary) as TotalSalary, avg(Salary) as avgSalary from [UserSalary] where 1=1"
sql=sql & " group by Department"
等同：sql="select Department ,count(id) as PepCount, sum(Salary) as TotalSalary, avg(Salary) as avgSalary from [UserSalary] group by Department"
"
response.Write("
数:"&rs("PepCount")&" 总工资:"&rs("TotalSalary")&" 平均工资:"&rs("avgSalary")&"
5.多表同时查询,通过评论中的产品ID,显示产品表中的产品名称.
sql="select ProductId,UserComment.Content,Product.id as pid,
Product.ProductName,UserComment.id as uid from UserComment,Product where UserComment.ProductId = Product.id"
response.Write("