目录
一、日期相关
1.查询当前日期相关数据
SELECT * FROM Article WHERE Year(PublishTime) = Year(GETDATE()) --等于此年
SELECT * FROM Article WHERE CAST(PublishTime AS date) = CAST(GETDATE() AS DATE) --等于此年此月此日
SELECT * FROM Article WHERE PublishTime = GETDATE() --等于此年此月此日此时此分此秒2.查询特定时间区间
SELECT * FROM table WHERE Time BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59';3.时间加减法
（1）加法
【解说】将StartTime的值加上一天，并返回结果。
修改前 2023-11-30 11:04:15.040
修改后 2023-12-01 11:04:15.040
SELECT CAST(DATEADD(DAY, 1, StartTime) AS DATETIME) FROM TABLE（2）减法
--算出2025年元旦距离现在还有多少天（2025-01-01减去此时此刻得到的天数）
SELECT DATEDIFF(DAY,GETDATE(), '2025-01-01')4.格式化日期
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日') 二、数据类型相关
1.Int -> Decimal
SELECT cast(AGE as decimal(10,2)) FROM TABLE1【备注】
decimal(10, 3)的含义：10位数字 = 3位小数 + 7位整数
2.DateTime->OtherTime
SELECT TOP 1 PublishTime FROM Article WHERE PublishTime IS NOT NULL
SELECT TOP 1 CAST(PublishTime AS smalldatetime) AS DateHour FROM Article WHERE PublishTime IS NOT NULL
SELECT TOP 1 CAST(PublishTime AS date) AS Date FROM Article WHERE PublishTime IS NOT NULL
SELECT TOP 1 CAST(PublishTime AS time) AS Hour FROM Article WHERE PublishTime IS NOT NULL
3.DateTime->string
SELECT TOP 1
CONVERT(VARCHAR(10), (SELECT TOP 1 TIME FROM FLHappy), 120) AS FormattedDate,
CONVERT(VARCHAR(8), (SELECT TOP 1 TIME FROM FLHappy), 108) AS FormattedTime,
CONVERT(VARCHAR(19), (SELECT TOP 1 TIME FROM FLHappy), 120) AS FormattedDateTime;
三、条件判断相关
1.ISNULL
SELECT ISNULL(AGE ,0) FROM TABLE12.IF ELSE（单字段）
SELECT(CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他'
END
) FROM TABLE13.IF ELSE（多字段）
SELECT(CASE
WHEN AGE=13 THEN AGE
WHEN CLASS=2 THEN CLASS
ELSE '其他'
END
) FROM TABLE1【注意】不要漏了end
【口诀】一个case一个end，一个when跟一个then
四、子查询
【情景说明】
Student表
学生ID 学生姓名 学生班级（stu_id stu_name stu_class）
Score表
成绩ID 学生ID 是否及格(score_id stu_id ispass)要求：返回学生姓名、是否及格字段，其中筛选出来的数据要求ispass=1
（1）联表（推荐）
SELECT s.stu_name, sc.ispass
FROM Student s
INNER JOIN Score sc ON s.stu_id = sc.stu_id
WHERE sc.ispass = 1;（2）嵌套in / Exist（推荐）
SELECT stu_name, '1' AS ispass
FROM Student
WHERE stu_id IN (
SELECT stu_id
FROM Score
WHERE ispass = 1
);SELECT stu_name, '1' AS ispass
FROM Student s
WHERE EXISTS (
SELECT 1
FROM Score sc
WHERE sc.stu_id = s.stu_id AND sc.ispass = 1
);（3）公共表达式CTE
WITH CTE AS(
SELECT stu_name, (
SELECT ispass
FROM Score
WHERE Score.stu_id = Student.stu_id
) AS 'ispass'
FROM Student
)
SELECT * FROM CTE WHERE ispass=1【CTE模板语法】
WITH CTE AS (
SELECT * FROM TABLE1
)
SELECT * FROM CTE;WITH TEMP1 AS(
--SQL1
),TEMP2 AS(
--SQL2
)
SELECT * FROM TEMP2（4）SELECT MANY SQL
【不同条件写法】
SELECT
(SELECT COUNT(*) FROM [Millionaire].[dbo].[FLHappy] WHERE allnum LIKE '%01%') AS num01,
(SELECT COUNT(*) FROM [Millionaire].[dbo].[FLHappy] WHERE allnum LIKE '%02%') AS num02;【同条件写法】
SELECT
MAX(CASE WHEN TYPE = 1 THEN NAME ELSE NULL END) AS NAME,
MAX(CASE WHEN TYPE = 2 THEN NAME ELSE NULL END) AS UID
FROM STUDENT
WHERE SCHOOL='440' AND CODE = 'A'五、查询数据处理
1.STUFF
【数据表】SchoolData：id teachername student (老师对学生是一对多，这张表允许老师重复)
【需求】查询输出teachername和studentlist(逗号拼接，输出老师名字和该老师管辖的所有学生)
【注解】STUFF(原字符串，被替换的位置，替换的长度，替换的符号)
STUFF(..., 1, 1, '')：从结果的开头（位置1）移除1个长度的字符（即逗号）
SELECT
teachername,
STUFF(
(SELECT ',' + student
FROM SchoolData AS t2
WHERE t2.teachername = t1.teachername
FOR XML PATH('')), 1, 1, '') AS studentlist
FROM
SchoolData AS t1
WHERE 1=1
GROUP BY
teachername;【模板】大哥找小弟（逗号拼接）
提示：将大哥和小弟替换为你想查的字段即可。
SELECT
大哥,
COUNT(*),
STUFF(
(SELECT ',' + 小弟
FROM 表名 AS t2
WHERE 1=1 AND t2.大哥 = t1.大哥
FOR XML PATH('')), 1, 1, '') AS Alllist
FROM
表名 AS t1
WHERE 1=1
GROUP BY
大哥
ORDER BY
大哥示例效果：
【模板】拼接一列字段
--逗号拼接
SELECT STUFF((
SELECT ',' + 单独的字段名
FROM 表名
FOR XML PATH('')), 1, 1, '') AS T2.聚合函数(MAX SUM AVG COUNT)
（1）查询非goupby字段
【错误的写法】Group字段和Select字段不一致（我想显示出Score）
SELECT Name, Age, Score
FROM PERSON
GROUP BY Name, Age;【方法一】取MAX值
SELECT Name, Age, MAX(SCORE) AS Score
FROM PERSON
GROUP BY Name, Age;【方法二】子查询（如果可以的话）
SELECT Name,Age,(SELECT SCORE FROM SCORETABLE WHERE NAME=NAME) AS Score
FROM PERSON
GROUP BY Name,Age（2）查询聚合条件和别名字段
【引入】
SELECT
ID,
NAME,
MAX(SCORE),
(SELECT Code FROM TABLE2 WHERE ID = ID) AS Code
FROM TABLE
WHERE Code=500 AND SCORE > 60
GROUP BY
ID,
NAME这个SQL是不对的，不能对聚合函数和别名字段直接筛选，且ID=ID有歧义，解决方案有两个：
1.SQL外包一层
2.对于聚合函数用having筛选，对于别名字段在子查询SQL中过滤
（且都用T.ID特指是TABLE的ID）
（2-1）SQL外包一层
SELECT *
FROM (
SELECT
ID,
NAME,
MAX(SCORE) AS MaxScore,
(SELECT Code FROM TABLE2 WHERE ID = T.ID) AS Code
FROM TABLE T
GROUP BY ID, NAME
) AS SubQuery
WHERE Code = 500 AND MaxScore > 60;
--【注】包完之后一定要 'AS SubQuery'，否则报错（2-2）对于聚合函数用having筛选，对于别名字段在子查询SQL中过滤
SELECT
ID,
NAME,
MAX(SCORE) AS MaxScore,
(SELECT Code FROM TABLE2 WHERE ID = T.ID AND Code = 500) AS Code
FROM TABLE T
GROUP BY ID, NAME
HAVING MAX(SCORE) > 60;
--【注】HAVING SCORE > 60 是不对的。3.分页查询
【后端传回前端】总页数、当前页行数
DECLARE @page_index INT = 2;-- 第几页，前端传入
DECLARE @page_size INT = 10; -- 一页几条，前端传入
-- 执行查询并分页
select * from UserInfo
order by UserID
offset ((@page_index-1)*@page_size) rows
fetch next (@page_size) rows only4.字符串处理
替代
SELECT 'ABC123' AS DATA --输出【ABC123】
SELECT REPLACE('ABC123','ABC','D') AS DATA2 --后面代替前面，输出【D123】
SELECT SUBSTRING('ABC123',2,4) AS DATA3 --切片，从第2开始，切4个长度的区间的内容，输出【BC12】切片
假设你要从字符串 123 中单独查出 1、2 和 3
SELECT
SUBSTRING('123', 1, 1) AS FirstDigit, -- 取第一个字符
SUBSTRING('123', 2, 1) AS SecondDigit, -- 取第二个字符
SUBSTRING('123', 3, 1) AS ThirdDigit -- 取第三个字符5.标号RowNumber
示例：
SELECT *,
ROW_NUMBER() OVER (PARTITION BY SUMVALUE ORDER BY TIME DESC) AS RowNum
FROM FLLottery
ORDER BY TIME DESC;--此处无论是否order by 都不影响结果结果：
模板：
--根据某个字段分组，并根据另一个字段排序，返回排名（例如根据班级分组，并根据成绩大到小倒序排序，返回排名）
SELECT *,
ROW_NUMBER() OVER (PARTITION BY 分组字段 ORDER BY 排序字段 DESC) AS RowNum --DESC=排序方式是倒序
FROM 表名
6.一表多联
①两张表：
- userinfo：id username postcode (区邮编)
- placeinfo：id postcode type name pid (type=1是区，type=2是市，pid关联市和区)
②需求：
查出：用户，区名称，市名称
③SQL：
SELECT
u.id AS user_id,
u.username,
district.name AS district_name,
city.name AS city_name
FROM
userinfo u
JOIN
placeinfo district ON u.postcode = district.postcode AND district.type = 1
JOIN
placeinfo city ON district.pid = city.id AND city.type = 2;④linq写法：
var query = from user in userlist
let district = placelist.FirstOrDefault(p => p.Postcode == user.Postcode && p.Type == 1)
let city = district != null ? placelist.FirstOrDefault(p => p.Id == district.Pid && p.Type == 2) : null
select new
{
Username = user.Username,
DistrictName = district != null ? district.Name : "未知区",
CityName = city != null ? city.Name : "未知市"
};7.递归查询
【需求】查找 AmoebaID= 4408180d-f097-46ef-9512-8338a618bd0a 的所有后代记录
WITH CTE AS (
-- 初始查询：选取目标记录
SELECT *
FROM T_BD_Amoeba
WHERE AmoebaID = '4408180d-f097-46ef-9512-8338a618bd0a'
AND IsDeleted = 0 -- 确保查询到的记录没有被删除
UNION ALL
-- 递归部分：获取子记录
SELECT a.*
FROM T_BD_Amoeba a
INNER JOIN CTE b ON b.AmoebaCode = a.ParentAmoeba
WHERE a.IsDeleted = 0 -- 确保只查询未删除的子记录
)
-- 最终查询：获取所有后代记录的详细信息
SELECT d.AmoebaID,
d.AmoebaCode,
d.AmoebaName
FROM CTE d
六、批量更新数据
stu表
id name code
dic表
id code1 code2
我stu表装的code是code1，我想改为code2（code1和code2是一一对应的，应该如何改呢）
UPDATE stu
SET code = dic.code2
FROM stu
JOIN dic ON stu.code = dic.code1;
扫一扫
3370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
