SQL学习以及刷题技巧分享

---

sql技巧

技巧1：多表自连

判断某一组连续n行中的某字段是否相等(n数字要小)
下面是三表的

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l2.id = l1.id + 1
JOIN Logs l3 ON l3.id = l1.id + 2
WHERE l1.num = l2.num AND l2.num = l3.num;

⭐️ 技巧2：sql执行顺序

FROM → WHERE → GROUP BY → HAVING → 窗口函数（OVER） → SELECT → ORDER BY → LIMIT

技巧3： from()创建一个临时表，表名t(必须命名),可以不写as。select 里取别名也可以不使用as

	select id ids from user u ;  //id会被别名为ids,user表名被别名为u

技巧4：创建临时表

表语句用括号括起来，然后空格加一个表名

FROM (
  SELECT num,
         LEAD(num, 1) OVER (ORDER BY id) AS next1,
         LEAD(num, 2) OVER (ORDER BY id) AS next2
  FROM Logs
)   t

函数

日期函数

• ⭐️Year(date)
  含义：返回日期的年份部分,若日期为 NULL 返回 NULL

	YEAR('2017-06-15 09:34:21');  -- 返回 2017
	YEAR(NULL);                    -- 返回 NULL

• ⭐️Month(date)
  含义：返回日期的月份部分，若日期为 NULL 返回 NULL

	MONTH('2025-07-09');  -- 返回 7

• ⭐️Day(date)
  含义：返回日期的天数部分，若日期为 NULL 返回 NULL

	 DAY('2025-07-09');  

• Current_date(date)
  含义：返回当前系统日期（不包含时间），格式 YYYY-MM-DD

	CURRENT_DATE();  -- 例如 '2025-07-09'` 

• Dayofweek(date)
  含义：返回星期几，1=周日，2=周一…7=周六

	DAYOFWEEK('2025-07-09');  -- 返回 4（周三）

• Date()
  含义：去除时间，返回日期部分 YYYY-MM-DD

	DATE('2025-07-09 15:23:45');  -- 返回 '2025-07-09'`

• Now()
  含义：返回当前日期和时间，格式 YYYY-MM-DD hh:mm:ss

	NOW();  -- 例如 '2025-07-09 

• Hour(datetime)
  含义：提取小时部分（0–23）

	HOUR('2025-07-09 15:23:45');  -- 返回 15`

• Minute(datetime)
  含义：提取分钟部分（0–59）

	MINUTE('2025-07-09 15:23:45');  -- 返回 23`

• Second(datetime)
  含义：提取秒钟部分（0–59）

	SECOND('2025-07-09 15:23:45');  -- 返回 45`

• ⭐️Datediff(date1,date2)
  含义：返回两个日期之间的天数差，即 date1 - date2

	DATEDIFF('2025-07-09','2025-07-01');  -- 返回 8`

• ⭐️Dateadd(date,interval expr unit) == date + interval expr unit
  含义：指定日期上增加时间单元

	DATE_ADD('2023-07-10', INTERVAL 7 DAY) -> '2023-07-17'
	'2023-07-10'+ INTERVAL 7 DAY -> '2023-07-17'

• ⭐️Date_sub(date, interval expr unit) == date - interval expr unit
  含义：从指定日期或日期时间中减去一定的时间间隔

	DATE_SUB('2023-01-31', INTERVAL 5 DAY);-- 返回: '2023-01-26'
	DATE_SUB('2020-11-22 09:12:10', INTERVAL 3 HOUR);-- 返回: '2020-11-22 06:12:10'
	'2023-07-10'- interval 7 day     - > '2023-07-03'

unit可以看以一下表格

分类	单位	说明
基础单位	MICROSECOND	微秒，最小时间单位，用于高精度时间运算，例如测量代码执行时间。
	SECOND	秒，用于秒级别的时间加减
	MINUTE	分钟，用于分钟级时间操作
	HOUR	小时，用于小时级时间加减
	⭐️ DAY	天，用于天数级时间运算
	WEEK	周（7 天）
	⭐️MONTH	月份
	QUARTER	季度（3 个月）
	⭐️YEAR	年
复合单位	SECOND_MICROSECOND	秒 + 微秒，例如 '1.500000' SECOND_MICROSECOND
	MINUTE_MICROSECOND	分钟 + 微秒，如 '1:02.000123' MINUTE_MICROSECOND
	MINUTE_SECOND	分钟 + 秒，如 '1:30' MINUTE_SECOND
	HOUR_MICROSECOND	小时 + 微秒，例如 '1:00:00.123456' HOUR_MICROSECOND
	HOUR_SECOND	小时 + 秒，如 '1:00:05' HOUR_SECOND
	HOUR_MINUTE	小时 + 分钟，例如 '1:30' HOUR_MINUTE
	DAY_MICROSECOND	天 + 微秒，如 '2 00:00:00.000123' DAY_MICROSECOND
	DAY_SECOND	天 + 秒，如 '2 01:03:04' DAY_SECOND
	DAY_MINUTE	天 + 分钟，例如 '2 01:03' DAY_MINUTE
	DAY_HOUR	天 + 小时，如 '2 05' DAY_HOUR
	YEAR_MONTH	年 + 月，例如 '1 3' YEAR_MONTH 表示 1 年 3 个月

文本函数

• LOWER()
  含义：转为小写

	LOWER('ABC') → 'abc'

• UPPER()
  含义：转为大写

	UPPER('abc') → 'ABC'

• LTRIM()
  含义：去除左侧空格

	LTRIM(' abc ') → 'abc '

• RTRIM()
  含义：去除右侧空格

	RTRIM(' abc ') → ' abc'

• TRIM()
  含义：去掉两侧空格

	TRIM('  hello world  ') → 'hello world'

• LENGTH()
  含义：获取字符串的长度

	LENGTH('abc') → 3; LENGTH('汉字') → 6

• LEFT(str, n)
  含义：从左截 n 个字符

	LEFT('abc123',3) → 'abc'

• RIGHT(str, n)
  含义：从右截 n 个字符

	RIGHT('abc123',3) → '123'

• SUBSTRING(str, pos, len)`
  含义：从 pos 起截 len 长

	SUBSTRING('abc123',2,3) → 'bc1'

• ⭐️CONCAT(str1, ...) 常用
  含义：拼接字符串

	CONCAT('abc','123','xyz') → 'abc123xyz'

数学函数

• ABS(x)
  含义：返回 x 的绝对值

	ABS(-3) → 3

• ⭐️round(x, d) 常用
  含义：将 x 四舍五入到 d 位小数

	round(1.236, 2) → 1.24

• mod(a,b)
  含义：取余运算(相当与%)

	mod(8, 2)   - > 0

⭐️聚合函数

• count() 计数(只要不为空都会计数)，sum(表达式),累加满足条件的行数
• sum() 求和
• avg() 求平均值
• max() 求最大值
• min() 求最小值

聚合函数小技巧：

1. 可以在聚合函数里去重（除max(),min()），如：count(distinct user_id)
2. where里不能使用聚合函数，要使用的话在having或者select里使用

⭐️窗口函数

注意：聚合函数都可以变成窗口函数
格式

	<窗口函数>() OVER (
    [PARTITION BY <分组列1>, <分组列2>, …]
    [ORDER BY <排序列> [ASC|DESC], …]
    [ROWS|RANGE <窗口帧定义>]
)

作用：

1. 可以给select 后面加字段(不会影响原先数据)
2. 不能在<窗口函数>()用distinct 如：sum(distinct id)

窗口函数

函数	作用
ROW_NUMBER() OVER(...)	为分区内的每一行分配唯一且连续的序号
RANK() OVER(...)	按指定排序给分区内行排名，相同值并列时会跳号（如 1,1,3…）
DENSE_RANK() OVER(...)	按指定排序给分区内行排名，相同值并列时不跳号（如 1,1,2…）
NTILE(n) OVER(...)	将分区内行分成 n 个桶，返回当前行所属的桶编号
LAG(expr, offset, default) OVER(...)	获取当前行之前 offset 行的 expr 值；若超出范围则返回 default
LEAD(expr, offset, default) OVER(...)	获取当前行之后 offset 行的 expr 值；若超出范围则返回 default
FIRST_VALUE(expr) OVER(...)	返回窗口帧（按照 PARTITION/BY+ORDER/BY+帧定义）范围内的第一个 expr 值
LAST_VALUE(expr) OVER(...)	返回窗口帧范围内的最后一个 expr 值
SUM(expr) OVER(...)	对窗口帧内的 expr 值求和
AVG(expr) OVER(...)	对窗口帧内的 expr 值求平均
COUNT(expr) OVER(...)	统计窗口帧内非空 expr 值的行数
MAX(expr)/MIN(expr) OVER(...)	取窗口帧内 expr 值的最大值或最小值

其他常用函数

• IF(condition, value_if_true, value_if_false)
  含义：根据条件返回不同的值

	IF(score >= 60, 'PASS', 'FAIL')

• ifnull(notNull,null)
  含义：专门处理空值的函数

	ifnull(1,0)    - > 为空时：0，不为空时：1

---

如果你在阅读过程中也有新的见解，或者遇到类似问题，🥰不妨留言分享你的经验，让大家一起学习。

喜欢本篇内容的朋友，记得点个 👍点赞，收藏 并 关注我，这样你就不会错过后续的更多实用技巧和深度干货了！

期待在评论区看到你的声音，我们一起成长、共同进步！😊