SQL 总结，保证你轻松刷过leetcode

有点标题党了，这个是我自己刷完leetcode的总结。
题目内容使用的function其实就是有限的，掌握了大概的几种，就都会做了。效率不敢保证，但是一定能做出来。
给跟我一样从头学起的sql小白的，大神请指点怎样改进。
谢谢啦！求大米！

Functions
CASE WHEN
Whereas CASE allows you to do different tests with each WHEN clause
CASE
WHEN A = B THEN C
WHEN D != F THEN E
ELSE Q
# use case when in select
SELECT
type,
SUM(CASE WHEN processed THEN 1 ELSE 0 END) :: NUMERIC /
COUNT(*) AS processed_rate
FROM
facebook_complaints
GROUP BY
type
SELECT count(CASE
                 WHEN POSITION <= 3
                      AND has_clicked = 'yes' THEN e.search_id
                 ELSE NULL
             END) * 1.0 / COUNT(r.result_id) * 100 AS percentage
FROM fb_search_results r
LEFT JOIN fb_search_events e ON r.result_id = e.search_id
ORDER BY ASC/ DESC
NOT IN
SELECT customers.first_name
FROM customers
WHERE customers.id NOT IN
    (SELECT orders.cust_id
     FROM orders
     WHERE order_date BETWEEN '2019-02-01' AND '2019-03-01' )

IN
WHERE customers.first_name IN ('Jill', 'Eva')
i.e. use it in subquery:
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
);

OR
select a.first_name, b.order_date, b.order_details, b.order_cost
from customers a
left join orders b
on a.id = b.cust_id
where a.first_name = 'Jill' or a.first_name = 'Eva'
order by a.id;

TOP
SELECT TOP(5) artist
FROM artists;

TOP PERCENT
SELECT TOP(5) PERCENT artist
FROM artists;

NULL
release_year IS NOT NULL;
ISNULL v.s. IFNULL
IFNULL(sum(r.distance), 0)
IFNULL Return Boolean

COALESCE/ ISNULL
SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com')

LIKE
WHERE description LIKE '%Weather%'

OFFSET
You can also specify an OFFSET from where to start returning data.
SELECT * FROM artists LIMIT 5 OFFSET 2;

Row Number
row_number()

Rank
Rank()

Regex
SELECT *
FROM Users
WHERE mail REGEXP '^[a-zA-Z]+[a-zA-Z0-9_.\-]*@leetcode.com$'

DENSE RANK
This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Example:
select Salary, dense_rank() over (order by Salary desc) as rc from Employee;
SELECT Id, DepartmentId, Name AS Employee, Salary, dense_rank () over   (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rc FROM Employee;
https://docs.microsoft.com/en-us ... ew=sql-server-ver15

UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.
•        Each SELECT statement within UNION must have the same number of columns
•        The columns must also have similar data types
•        The columns in each SELECT statement must also be in the same order

UNION ALL: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

String manipulation
substring(order_date, 6, 2)
# split_part extract ‘’
count(distinct lower(split_part(business_address, ' ', 2)))
# concat string
concat(upper(left(name, 1)), lower(right(name, length(name)-1)))
# group concat
GROUP_CONCAT(DISTINCT a.PRODUCT ORDER BY a.PRODUCT) AS PRODUCTS

Calculation Operation
ROUND(Duration, 0) AS RoundToZero;
ROUND(Duration, -1) AS RoundToTen;
ROUND(Duration, 0, 1) AS RoundtoTenth
ABS(), SQUARE(), SQRT(), STDEV()
P1.x != p1.y or P2.x != p2.y
(p1.x,p1.y) != (p2.x,p2.y)
power(p1.x - p2.x,2)

.1point3acresData Type  
# sometime data type is the key
select city, avg(accommodates/beds :: float) as avg_ratio
from airbnb_search_details
where room_type = 'Shared room'
. 1point 3 acresgroup by city
order by avg_ratio desc;

Calculation Date
DATEADD(DATAPART, number, date):
SELECT DATEADD(DD, 30, ‘2020-06-21’)
DATEDIFF(datepart, startdate, enddate):
SELECT OrderDate, ShipDate,
       DATEDIFF(D, OrderDate, ShipDate) AS Duration
FROM Shipments
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;
EXTRACT(month from order_date :: timestamp)=3
# extract/ substring should only be in select/ careful looking at date or string!
select EXTRACT (YEAR FROM inspection_date :: DATE) AS year,
    count(*)
from sf_restaurant_health_violations
where business_name = 'Roxanne Cafe' AND
    risk_category IS NOT NULL
group by year
order by year;

Subquery
WITH CTEName (Col1, Col2)
AS
(         
           SELECT
           FROM
)

Window and Partitions
OVER (PARTITION BY SalesYear ORDER BY SalesYear)
LEAD(CurrentQuota) OVER (PARTITION BY SalesYear ORDER BY ModifiedDate)
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY OrderDate)
RANK() over (partition by a.customer_id order by count(1) desc)
SUM (StudentAge) OVER (ORDER BY Id)
Over clause can be used to select non aggregated values along with Aggregated ones.
Partition BY, ORDER BY inside, and ROWS or RANGE are part of OVER() by clause.
partition by is used to partition data and then perform these window, aggregated functions, and if we don't have partition by the then entire result set is considered as a single partition.
OVER clause can be used with Ranking Functions(Rank, Row_Number, Dense_Rank..), Aggregate Functions like (AVG, Max, Min, SUM...etc) and Analytics Functions like (First_Value, Last_Value, and few others).
Let's See basic syntax of OVER clause
OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  
PARTITION BY: It is used to partition data and perform operations on groups with the same data.
ORDER BY: It is used to define the logical order of data in Partitions. When we don't specify Partition, entire resultset is considered as a single partition
select id, login_date, lead(login_date, 4) over (partition by id order by login_date) fifth_login
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
UNBOUNDED PRECEDING
Select
    item_category as category,
    coalesce(SUM(case when date_format(order_date, '%a') = 'Mon' then coalesce(quantity,0) END),0)AS  'MONDAY',
    coalesce(SUM(case when date_format(order_date, '%a') = 'Tue' then coalesce(quantity,0) END),0) AS  'TUESDAY',
    coalesce(SUM(case when date_format(order_date, '%a') = 'Wed' then coalesce(quantity,0) END),0) AS  'WEDNESDAY',
    coalesce(SUM(case when date_format(order_date, '%a') = 'Thu' then coalesce(quantity,0) END),0) AS  'THURSDAY',
    coalesce(SUM(case when date_format(order_date, '%a') = 'Fri' then coalesce(quantity,0) END),0) AS  'FRIDAY',
    coalesce(SUM(case when date_format(order_date, '%a') = 'Sat' then coalesce(quantity,0) END),0) AS  'SATURDAY',
    coalesce(SUM(case when date_format(order_date, '%a') = 'Sun' then coalesce(quantity,0) END),0) AS  'SUNDAY'
from items i
Left outer join orders o
on o.item_id = i.item_id
group by item_category
order by category
https://docs.aws.amazon.com/reds ... ction_synopsis.html

WITH
with EmployeeSalary as(
select Salary, dense_rank() over (order by Salary desc) as rc
from Employee
. check 1point3acres for more.)

Tops and Tricks
# remember to sort see if we need to include name/ product that has zero records.
# sub query must have alias!!
# user is system defined name
# pay attention to distinct, order by
•        
SELECT …
FROM Employee as E1, Department as D
WHERE
(
SELECT Count (Distinct E2.Salary)
FROM Employee as E2
WHERE E1.DepartmentId = E2.DepartmentId and E2.Salary > E1.Salary
) < 3
AND E1.DepartmentId = D.Id
•        
SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1
•        
select project_id
from project
group by project_id
having count(employee_id) >= all(select count(employee_id) from project group by project_id)

Woc楼主我爱你 正好快有个Sql面 让我刷到这个现成的资料复习一下 太感谢了

赞一个 就是这个情况 速度差不多的一星期就刷通SQL了

mark， 多谢楼主总结，最近也在补sql的知识，一起愉快的刷题吧

谢谢楼主，对面试很有帮助

MARK 一下

马一个
哈哈哈哈哈 谢谢楼主

mark了！感谢楼主分享！leetcode刷起来

谢谢LZ的总结！

感谢楼主的总结 给楼主加米了

感谢楼主的总结

谢谢楼主！