关于sql:SQL笔记

Limit Offset
return only 10 records, start on record 16 (OFFSET 15)
SELECT * FROM Orders LIMIT 10 OFFSET 15
Order
order by points desc, goal_diff desc, team_name;

Join
can have >, < conditions in join

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;

having

select Email
from Person
group by Email
having count(Email) > 1;

window
rank() over (partition by departmentId order by salary desc) as salaryRank
dense_rank()
lead(num,1) over(order by id) as next_num_1
lead(num,2) over(order by id) as next_num_2
lag(temperature) over (order by recordDate) as previous_temperature
CTE common table expression
with t1 as (…)
select * from t1

with t1 as (…),
t2 as ()
select * from t1
union all
select * from t2

Recursive CTE
WITH RECURSIVE CTE AS (…)

IFNULL / coalesce
IFNULL(…,default_value_if_null)
SELECT

  IFNULL(
    (SELECT DISTINCT Salary
     FROM Employee
     ORDER BY Salary DESC
      LIMIT 1 OFFSET 1),
  NULL) AS SecondHighestSalary

coalesce(round(avg(accepted),2),0)

case
select

  case when MOD(id,2)=1 and id=(select max(id) from Seat) then id 
   when MOD(id,2)=1 then id+1 
   when MOD(id,2)=0 then id-1 
   end as new_id

from Seat

Delete

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

Update

update Salary
set sex = case when sex='m' then 'f' else 'm' end

Other
Request_day BETWEEN ‘2013-10-01’ AND ‘2013-10-03’
cast(count(*) as float)
ROUND(COUNT(IF(Status != ‘completed’, TRUE, NULL)) / COUNT(*), 2)
Datediff(recordDate,previous_recordDate)=1
Date(call_time)
year(order_date)=2019
MOD(id,2)=1
substring(phone_number,1,3)
Subtract 10 days from a date and return the date: DATE_SUB(“2017-06-15”, INTERVAL 10 DAY)
LEAST(x,y): return the minimum of x and y
GREATEST(x,y): return the maximum of x and y
SELECT GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ‘,’) AS products

Function

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
Declare n int default N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct salary 
      from employee
      order by salary desc
      limit 1 offset n
  );
END

Pivot
CREATE PROCEDURE PivotProducts()
BEGIN

#Override GROUP_CONCAT length which has a default limit of 1024
SET SESSION group_concat_max_len = 1000000;

#Store case statement for dynamically generated columns in a variable ie case_stmt
SET @case_stmt = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT(‘SUM(CASE WHEN store = “‘, store, ‘” THEN price END) AS ‘, store))
INTO @case_stmt
FROM products;

#Insert above statement (@case_stmt) in the following main query to frame final query
SET @sql_query = CONCAT(‘SELECT product_id, ‘, @case_stmt, ‘ FROM products GROUP BY product_id’);

#Execute final query
PREPARE final_sql_query FROM @sql_query;
EXECUTE final_sql_query;
DEALLOCATE PREPARE final_sql_query;

END

(262. Trips and Users
(2252. Dynamic Pivoting of a Table
(1384. Total Sales Amount by Year
(2153. The Number of Passengers in Each Bus II

Leetcode