mysql优化_mysql sql优化_mysql优化面试题(13)

10、一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户。

select count(*) as num,tb.id

from

tb,

(select role from tb where id=xxx) as t1

where

tb.role = t1.role and tb.id != t1.id

group by tb.id

having

num = select count(role) from tb where id=xxx;

8. xxx公司的sql面试

Table EMPLOYEES Structure:

EMPLOYEE_ID NUMBER Primary Key,

FIRST_NAME VARCHAR2(25),

LAST_NAME VARCHAR2(25),

Salary number(8,2),

HiredDate DATE,

Departmentid number(2)

Table Departments Structure:

Departmentid number(2) Primary Key,

DepartmentName VARCHAR2(25).

(2）基于上述EMPLOYEES表写出查询：写出雇用日期在今年的，或者工资在[1000,2000]之间的，或者员工姓名（last_name）以’Obama’打头的所有员工，列出这些员工的全部个人信息。（4分）

select * from employees

where Year(hiredDate) = Year(date())

or (salary between 1000 and 200)

or left(last_name,3)='abc';

(3)基于上述EMPLOYEES表写出查询：查出部门平均工资大于1800元的部门的所有员工，列出这些员工的全部个人信息。（4分）

mysql> select id,name,salary,deptid did from employee1 where(select avg(salary)

from employee1 where deptid = did) > 1800;

(4)基于上述EMPLOYEES表写出查询：查出个人工资高于其所在部门平均工资的员工，列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。（5分）

selectemployee1.*,(employee1.salary-t.avgSalary)*100/employee1.salary

from employee1,

(select deptid,avg(salary) avgSalary from employee1 group bydeptid) as t

where employee1.deptid = t.deptid andemployee1.salary>t.avgSalary;

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-34607-13.html