sql多表分组查询 group by 表别名

Sql多表分组查询

表:UserSalary

1.显示最高工资人员信息:

sql="select UserName,UserSex,Department,Salary ,Date from [UserSalary] where Salary=(select Max(Salary) from UserSalary)"

response.Write("-"&rs("UserName")&""&rs("UserSex")&""&rs("Department") &""&rs("Salary")&""&rs("Date")&"")

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("Department")&"每个部门员工

数:"&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(""&rs("ProductId")&""&rs("Content")&""&rs("ProductName")&"-"&rs("pid")&""&rs("uid")&"")