SQL 刷题技巧总结
#SQL 2024-06-05

记录一下常用的 SQL 操作。

时间处理

DATEDIFF

用于计算两者的日期差 

1
DATEDIFF(date1,date2)
date1 和 date2 参数是合法的日期或日期/时间表达式。

注释：只有值的日期部分参与计算。 

1
2
DATEDIFF('2007-12-31','2007-12-30');   # 1
DATEDIFF('2010-12-30','2010-12-31');   # -1

TIMESTAMPDIFF

用于计算两者的时间差 

1
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

1
2
3
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');  # 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');  # -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); # 128885

DATE_ADD

给日期添加指定的时间间隔

1
DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。 type 参数可以是下列值： | type | |--| MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH 

1
2
# 最小 event_date +3 天作为 second_date
SELECT DATE_ADD(MIN(event_date), INTERVAL 3 DAY) AS second_date
## DATE_SUB 从日期减去指定的时间间隔

其他

ROUND

对某个数值域进行指定小数位数的四舍五入 

1
ROUND(c,decimals)

IF

1
IF(condition, value_if_true, value_if_false)
1
SELECT IF(500<1000, "YES", "NO");

IFNULL

1
IFNULL(expression, alt_value)

当 expression 为 NULL，返回 alt_value 

1
SELECT IFNULL(NULL, "W3Schools.com");

CASE

1
2
3
4
5
6
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;
1
2
3
4
5
6
7
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

开窗函数

1
2
3
SELECT 
聚合函数 OVER(PARTITION BY condition1 ORDER BY condition2)
FROM table;
1
2
3
4
5
SELECT
year, country, product, profit,
ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
FROM sales;