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 ;
【SQL笔记】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