当前位置：首页 -> AI编程基础 -> 数据库编程

LeetcodeDatabase-我的汇总(二)

2015-11-21 01:31:10 · 作者: · 浏览: 18
标签: LeetcodeDatabase- 汇总
  1. 第一名 99
  2. 第二名 98
  3. 第二名 98
  4. 第四名 97
  1. 第一名 99
  2. 第二名 98
  3. 第二名 98
  4. 第三名 97
  1. SELECT d.Name Department, temp.Name Employee, temp.Salary Salary
  2. FROM Department d,
  3. ( SELECT em.Id Id,em.Name Name,em.Salary Salary,em.DepartmentId dep
  4. FROM Employee em
  5. WHERE (
  6. #count records whose salary large than outer salary
  7. SELECT count(1) FROM Employee emp
  8. WHERE em.DepartmentId=emp.DepartmentId
  9. And em.Salary
  10. )<=2) temp
  11. #for record with highest salary, 0 record large than it; for record with second highest #salary, 1 record large than it. To get top three, records large than target record should #<=2
  12. WHERE temp.dep=d.Id
  13. ORDER BY Department ,Salary DESC;
  14. #in this way, as ‘count’ does not remove duplicate records, there will be a gap after #records with same salary
  1. SELECT d.Name Department, temp.Name Employee, temp.Salary Salary
  2. FROM Department d,
  3. ( SELECT em.Id Id,em.Name Name,em.Salary Salary,em.DepartmentId dep
  4. FROM Employee em
  5. WHERE (
  6. #with ‘count distinct’, the result is same as with no duplicate records
  7. SELECT count(distinct emp.Salary) FROM Employee emp
  8. WHERE em.DepartmentId=emp.DepartmentId
  9. And em.Salary<=emp.Salary
  10. )<=3) temp
  11. WHERE temp.dep=d.Id
  12. ORDER BY Department ,Salary DESC;
  1. select a.depart, a.name, a.salary
  2. from (
  3. select d.name depart, e.name name, rank()over(partition by d.id order by e.salary desc) rank, e.salary salary
  4. from department d, employee e
  5. where d.id=e.departmentid
  6. ) a
  7. where a.rank<=3;

  1. create table Logs(id int,num int);

  2. insert into Logs(id,num) values(1,1);

  3. insert into Logs(id,num) values(2,1);

  4. insert into Logs(id,num) values(3,1);

  5. insert into Logs(id,num) values(4,2);

  6. insert into Logs(id,num) values(5,1);

  7. insert into Logs(id,num) values(6,2);

  8. insert into Logs(id,num) values(7,2);
  1. Select distinct la.num
  2. From Logs la ,Logs lb,Logs lc
  3. where la.id+1=lb.id and lb.id+1=lc.id and la.num=lb.num and lb.num=lc.num;
  1. Select distinct la.num
  2. From Logs la
  3. Where(
  4. #if all the three records have same num value, the ‘count’get result 1
  5. Select count(distinct lb.num)
  6. From Logs lb
  7. Where la.id<=lb.id and lb.id<=la.id+2
  8. #filter records with id between la.id and la.id+2, get no more than 3 records
  9. )=1;
  1. Select distinct la.num
  2. From Logs la
  3. Where(
  4. #if all the three records have same num value, the ‘count’get result 1
  5. Select count(distinct lb.num)
  6. From Logs lb
  7. Where la.id<=lb.id and lb.id<=la.id+2
  8. #filter records with id between la.id and la.id+2, get no more than 3 records
  9. )=1
  10. and
  11. # make sure 3 rows selected
  12. ( Select count(1)
  13. From Logs lb
  14. Where la.id<=lb.id and lb.id<=la.id+2 )=3;

  1. ?
  1. SELECT dep.name depart, ttm.na employee, ttm.sal salary
  2. FROM Department dep,(
  3. SELECT emp.Name na, emp.salary sal, emp.departmentid dd
  4. FROM Employee emp,(
  5. SELECT departmentid did, MAX(salary) ms
  6. FROM Employee
  7. GROUP BY departmentid) temp
  8. WHERE emp.salary=temp.ms
  9. AND temp.did=emp.departmentid) ttm
  10. WHERE dep.id=ttm.dd;
  1. select dep.name department, max(em.salary) salary
  2. from Employee em, Department dep
  3. where em.departmentid=dep.id
  4. group by dep.name ;
  1. ?
  1. select eb.dname department, ea.name employee, ea.salary salary
  2. from employee ea, (
  3. select max(salary) ms, dep.name dname
  4. from Employee em, Department dep
  5. where em.departmentid=dep.id
  6. group by em.departmentid ) eb
  7. where ea.salary=eb.ms;
  1. ?

  1. DROP TABLE Employee PUGRE;

  2. CREATE TABLE Employee(id INT, salary INT);

  3. INSERT INTO Employee(id,salary) VALUES(1,100);
  1. SELECT MAX(em.salary)
  2. FROM Employee em
  3. WHERE em.