- DROP TABLE weather PURGE;
- CREATE TABLE weather(id INT PRIMARY KEY, recorddate DATE, temperature INT );
- INSERT INTO weather(id, recorddate, temperature) VALUES(1, SYSDATE,10);
- INSERT INTO weather(id, recorddate, temperature) VALUES(2, SYSDATE+1,25);
- INSERT INTO weather(id, recorddate, temperature) VALUES(3, SYSDATE+2,20);
- INSERT INTO weather(id, recorddate, temperature) VALUES(4, SYSDATE+3,30);
|
- SELECT a.id
- FROM weather a, weather b
- WHERE to_date(a.recorddate-1 ,'YYYY-MM-DD')=to_date(b.recorddate ,'YYYY-MM-DD')
- AND a.temperature>b.temperature;
|
- SELECT a.Id
- FROM Weather a, Weather b
- WHERE TO_DAYS(a.Date)-1=TO_DAYS(b.Date) AND a.Temperature>b.Temperature;
|
- Oracle
|
- mySQL
|
- to_date(date)
|
- TO_DAYS(date)
|
- to_date( '2008-09-08')
|
- ?
|
-
DROP TABLE Customers PURGE;
-
CREATE TABLE Customers ( Id INT PRIMARY KEY, Name CHAR(20) );
-
INSERT INTO Customers (Id, Name) VALUES(1,'joe');
-
INSERT INTO Customers (Id, Name) VALUES(2,'henry');
-
INSERT INTO Customers (Id, Name) VALUES(3,'sam');
-
INSERT INTO Customers (Id, Name) VALUES(4,'max');
-
?
-
DROP TABLE Orders PURGE;
-
CREATE TABLE Orders ( Id INT PRIMARY KEY, CustomerId INT ) ;
-
INSERT INTO Orders (Id, CustomerId) VALUES(1,3);
-
INSERT INTO Orders (Id, CustomerId) VALUES(2,1);
|
- SELECT Name From Customers
- WHERE ( SELECT COUNT(1) FROM Orders WHERE Orders.CustomerId=Customers.Id) = 0;
|
-
DROP TABLE Scores PURGE;
-
CREATE TABLE Scores
-
(
-
Id INT PRIMARY KEY,
-
Score FLOAT
-
);
-
INSERT INTO Scores(Id, Score) VALUES(1,3.59);
-
INSERT INTO Scores(Id, Score) VALUES(2,3.65);
-
INSERT INTO Scores(Id, Score) VALUES(3,4.00);
-
INSERT INTO Scores(Id, Score) VALUES(4,3.85);
-
INSERT INTO Scores(Id, Score) VALUES(5,4.00);
-
INSERT INTO Scores(Id, Score) VALUES(6,3.65);
|
-
SELECT a.Score, b.rank
-
FROM Scores a, (
-
SELECT sst.Id, COUNT(temp.ts) +1 rank
-
FROM Scores sst, (
-
SELECT DISTINCT Score ts FROM Scores) temp
-
WHERE sst.Score
- ?
- ?
-
GROUP BY sst.Id ) b
WHERE a.Id=b.Id
ORDER BY a.Score DESC;
|
- SELECT a.Score, b.rank
- FROM Scores a, (
- SELECT sst.Id,COUNT(temp.ts)+1 rank
- FROM Scores sst LEFT JOIN (SELECT DISTINCT Score ts FROM Scores) temp
- ON sst.Score < temp.ts
- GROUP BY sst.Id) b
- WHERE a.Id=b.Id
- ORDER BY a.Score DESC;
|
- Oracle left join
|
- mySQL left jion
|
- table_a.column = table_b.column(+)
|
- table_a LEFT JOIN table_b
- ON CONDITION
|
- select score, dense_rank()over(order by score desc) from Scores ;
|
- DROP TABLE Employee PURGE;
- CREATE TABLE Employee ( Id INT PRIMARY KEY, Name CHAR(20), Salary INT, DepartmentId INT );
- INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(1,'joe',70000,1);
- INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(2,'henry',80000,2);
- INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(3,'sam',60000,2);
- INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(4,'Max',90000,1);
- INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(5,'Janet',69000,1);
- INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(6,'Randy',85000,1);
- INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(7,'rap',85000,1);
- ?
- DROP TABLE Department PURGE;
- CREATE TABLE Department ( Id INT PRIMARY KEY, Name CHAR(20) );
- INSERT INTO Department(Id, Name) VALUES(1,'IT');
- INSERT INTO Department(Id, Name) VALUES(2,'Sales');
|
|