webank之SQL题

 20190427更新 着重看第二题

实现分组有两种形式：1.groupby 2. where a.id=b.id

1.查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id （做对）

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN 
                  (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND     department_id IN 
                  (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND employee_id NOT IN(174,141);

2.返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

# 正确答案
SELECT  a.t_name, a.salary,
        a.department_id, b.salavg
FROM    employees a, (SELECT   department_id,
                      AVG(salary) salavg
                      FROM     employees
                      GROUP BY department_id) b
WHERE   a.department_id = b.department_id
AND     a.salary > b.salavg;

#另一种做法
SELECT  a.t_name, a.salary,
        a.department_id, b.salavg
FROM    employees a
JOIN (SELECT   department_id,
                      AVG(salary) salavg
                      FROM     employees
                      GROUP BY department_id) b
ON   a.department_id = b.department_id
AND     a.salary > b.salavg;

-- 看到了吗 用ON...AND啊！！

# 自己思路bug
SELECT e1.last_name,e1.department_id,e1.salary,avg(e1.salary)
  FROM employees e1,employees e2
 WHERE e1.empoyee_id = e2.empoyee_id
   AND e1.salary > (SELECT avg(salary) FROM employees e2
					 GROUP BY e2.department_id ) -- 这里不能用group by返回多个值肯定不对 

-- 可以改成where
-- 实现分组有两种方式：一种是group by一种是在子查询中where a.id=b.id
SELECT avg(salary) 
  FROM employees e2
WHERE e2.department_id = e1.department_id -- 但因为还要显示平均工资
--------------------自己的另一种正确做法
SELECT e1.last_name,e1.department_id,e1.salary,
       (SELECT avg(salary) FROM employees e2
					 WHERE e2.department_id = e1.department_id) AS avgsalary
 FROM employees e1
 WHERE e1.salary > (SELECT avg(salary) FROM employees e2
					 WHERE e2.department_id = e1.department_id) 

可以创建小实验说明自己其实做对了
CREATE TABLE employees
 (
 Id            INT          NOT NULL PRIMARY KEY,
 t_Name          VARCHAR(50)  NOT NULL,
 Salary        INT          NOT NULL,
 Departmentid  INT          NOT NULL

 )	;

 

INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (1,'Joe',70000,1);
INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (2,'Joeny',7000,1);
INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (3,'Joe',10000,1);
INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (4,'nry',80000,2);
INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (5,'Sam',6000,2);
INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (6,'Henry',30000,2);
INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (7,'Sam',60000,2);
INSERT INTO employees(Id,t_Name,Salary,Departmentid) VALUES (8,'Max',90000,1);

3. 显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’

SELECT e.employee_id,e.last_name,

 (CASE d.department_id WHEN (
                              SELECT department_id FROM departments
                              WHERE location_id = 1800) THEN 'Canada'
  ELSE 'USA' 
   END) location

  FROM empoyees e,department d

 WHERE e.department_id = d.department_id;


# 自己写的
SELECT e.employee_id,e.last_name,

 (CASE WHEN d.department_id =
                              (SELECT department_id FROM departments
                              WHERE location_id = 1800) THEN 'Canada'
  ELSE 'USA' 
   END) location

  FROM empoyees e,department d

 WHERE e.department_id = d.department_id;

感觉也对吧。。。

4.查询员工的employee_id,t_name,要求按照员工的department_name排序

#答案
SELECT   employee_id, t_name
FROM     employees e
ORDER BY (SELECT department_name
            FROM departments d
           WHERE e.department_id = d.department_id);

CREATE TABLE em(
e_id     INT    NOT NULL,
name   VARCHAR(15) NOT NULL,
d_id     INT    NOT NULL);

CREATE TABLE de(
d_id     INT    NOT NULL,
d_name   VARCHAR(15) NOT NULL
);


INSERT INTO em VALUES (1,'BOb',1); 
INSERT INTO em VALUES (2,'John',2); 
INSERT INTO em VALUES (3,'mary',2); 
INSERT INTO em VALUES (4,'kylin',2); 
INSERT INTO em VALUES (5,'lych',1); 
INSERT INTO em VALUES (6,'echo',3); 
INSERT INTO em VALUES (7,'linda',3); 

INSERT INTO de VALUES (1,'A'); 
INSERT INTO de VALUES (2,'B'); 
INSERT INTO de VALUES (3,'C');
# 答案做法
SELECT   e_id, name
FROM     em e
ORDER BY (SELECT d_name
            FROM de d
           WHERE e.d_id = d.d_id);

#我的做法     
SELECT   e.e_id, e.name
FROM     em e,de d
WHERE    e.d_id = d.d_id
ORDER BY d.d_name;

 最后的返回结果都对

但是我需要好好理解一下答案的做法

执行顺序from-where-group by-having-select-distinct-union-order by