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

LeetcodeDatabase-我的汇总(一)

2015-11-21 01:31:10 · 作者: · 浏览: 14
标签: LeetcodeDatabase- 汇总
  1. DROP TABLE weather PURGE;
  2. CREATE TABLE weather(id INT PRIMARY KEY, recorddate DATE, temperature INT );
  3. INSERT INTO weather(id, recorddate, temperature) VALUES(1, SYSDATE,10);
  4. INSERT INTO weather(id, recorddate, temperature) VALUES(2, SYSDATE+1,25);
  5. INSERT INTO weather(id, recorddate, temperature) VALUES(3, SYSDATE+2,20);
  6. INSERT INTO weather(id, recorddate, temperature) VALUES(4, SYSDATE+3,30);
  1. SELECT a.id
  2. FROM weather a, weather b
  3. WHERE to_date(a.recorddate-1 ,'YYYY-MM-DD')=to_date(b.recorddate ,'YYYY-MM-DD')
  4. AND a.temperature>b.temperature;
  1. SELECT a.Id
  2. FROM Weather a, Weather b
  3. WHERE TO_DAYS(a.Date)-1=TO_DAYS(b.Date) AND a.Temperature>b.Temperature;
  1. Oracle
  1. mySQL
  1. to_date(date)
  1. TO_DAYS(date)
  1. to_date( '2008-09-08')
  1. ?

  1. DROP TABLE Customers PURGE;

  2. CREATE TABLE Customers ( Id INT PRIMARY KEY, Name CHAR(20) );

  3. INSERT INTO Customers (Id, Name) VALUES(1,'joe');

  4. INSERT INTO Customers (Id, Name) VALUES(2,'henry');

  5. INSERT INTO Customers (Id, Name) VALUES(3,'sam');

  6. INSERT INTO Customers (Id, Name) VALUES(4,'max');

  7. ?

  8. DROP TABLE Orders PURGE;

  9. CREATE TABLE Orders ( Id INT PRIMARY KEY, CustomerId INT ) ;

  10. INSERT INTO Orders (Id, CustomerId) VALUES(1,3);

  11. INSERT INTO Orders (Id, CustomerId) VALUES(2,1);
  1. SELECT Name From Customers
  2. WHERE ( SELECT COUNT(1) FROM Orders WHERE Orders.CustomerId=Customers.Id) = 0;

  1. DROP TABLE Scores PURGE;

  2. CREATE TABLE Scores

  3. (

  4. Id INT PRIMARY KEY,

  5. Score FLOAT

  6. );

  7. INSERT INTO Scores(Id, Score) VALUES(1,3.59);

  8. INSERT INTO Scores(Id, Score) VALUES(2,3.65);

  9. INSERT INTO Scores(Id, Score) VALUES(3,4.00);

  10. INSERT INTO Scores(Id, Score) VALUES(4,3.85);

  11. INSERT INTO Scores(Id, Score) VALUES(5,4.00);

  12. INSERT INTO Scores(Id, Score) VALUES(6,3.65);

  1. SELECT a.Score, b.rank

  2. FROM Scores a, (

  3. SELECT sst.Id, COUNT(temp.ts) +1 rank

  4. FROM Scores sst, (

  5. SELECT DISTINCT Score ts FROM Scores) temp

  6. WHERE sst.Score

  7. ?
  8. ?

  9. GROUP BY sst.Id ) b

    WHERE a.Id=b.Id

    ORDER BY a.Score DESC;
  1. SELECT a.Score, b.rank
  2. FROM Scores a, (
  3. SELECT sst.Id,COUNT(temp.ts)+1 rank
  4. FROM Scores sst LEFT JOIN (SELECT DISTINCT Score ts FROM Scores) temp
  5. ON sst.Score < temp.ts
  6. GROUP BY sst.Id) b
  7. WHERE a.Id=b.Id
  8. ORDER BY a.Score DESC;
  1. Oracle left join
  1. mySQL left jion
  1. table_a.column = table_b.column(+)
  1. table_a LEFT JOIN table_b
  2. ON CONDITION
  1. select score, dense_rank()over(order by score desc) from Scores ;
  1. DROP TABLE Employee PURGE;
  2. CREATE TABLE Employee ( Id INT PRIMARY KEY, Name CHAR(20), Salary INT, DepartmentId INT );
  3. INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(1,'joe',70000,1);
  4. INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(2,'henry',80000,2);
  5. INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(3,'sam',60000,2);
  6. INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(4,'Max',90000,1);
  7. INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(5,'Janet',69000,1);
  8. INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(6,'Randy',85000,1);
  9. INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(7,'rap',85000,1);
  10. ?
  11. DROP TABLE Department PURGE;
  12. CREATE TABLE Department ( Id INT PRIMARY KEY, Name CHAR(20) );
  13. INSERT INTO Department(Id, Name) VALUES(1,'IT');
  14. INSERT INTO Department(Id, Name) VALUES(2,'Sales');
  1. 有间隔的方法
  1. 无间隔的方法