关于sql:SQL笔记

61次阅读

共计 2507 个字符,预计需要花费 7 分钟才能阅读完成。

  • 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

Leetcode

(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

正文完
 0