MSSQL03:SQLServer数据库中的高级语法及其技巧

目录一、日期相关1.查询当前日期相关数据2.查询特定时间区间3.时间加减法1加法2减法4.格式化日期二、数据类型转化1.Int - Decimal2.DateTime-OtherTime3.DateTime-string三、条件判断相关(case when)1.IsNull2.IsNullOrEmpty3.IF ELSE单字段4.IF ELSE多字段四、公共表达式CTE五、SELECT MANY SQL六、逗号拼接STUFF七、列的计算和作差八、分组查询含窗口函数九、递归查询十、表的自连接同比环比、层级查询一、日期相关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.040SELECT CAST(DATEADD(DAY, 1, StartTime) AS DATETIME) FROM TABLE2减法--算出2025年元旦距离现在还有多少天2025-01-01减去此时此刻得到的天数 SELECT DATEDIFF(DAY,GETDATE(), 2025-01-01)4.格式化日期SELECT FORMAT(GETDATE(), yyyy年MM月dd日)二、数据类型转化1.Int - DecimalSELECT cast(AGE as decimal(10,2)) FROM TABLE1【备注】decimal(10, 3)的含义10位数字 3位小数 7位整数2.DateTime-OtherTimeSELECT 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 NULL3.DateTime-stringSELECT 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;三、条件判断相关(case when)1.IsNullAge为Null 则返回0SELECT ISNULL(AGE ,0) FROM TABLE12.IsNullOrEmptyAge为Null或者空字符串 则返回Description字段SELECT COALESCE(NULLIF(AGE, ), Description) FROM TABLE1;3.IF ELSE单字段-- 单字段单值判断 CASE WHEN sex1 THEN 男 ELSE 女 END -- 单字段多值判断 CASE sex WHEN 1 THEN 男 WHEN 2 THEN 女 ELSE 其他 END4.IF ELSE多字段SELECT(CASE WHEN AGE13 THEN AGE WHEN CLASS2 THEN CLASS ELSE 其他 END ) FROM TABLE1【注意】不要漏了end【口诀】一个case一个end一个when跟一个then解说Case本质是多加一列select *, (CASE WHEN Score 60 THEN 1 ELSE 0 END) AS IsPassed from StudentScore四、公共表达式CTE公用表表达式CTE的作用域仅限于紧随其后的一个SQL语句,若想长时间生效可考虑将结果插入到临时表。【CTE模板语法】WITH CTE AS ( SELECT * FROM TABLE1 ) SELECT * FROM CTE;WITH TEMP1 AS( --SQL1 ),TEMP2 AS( --SQL2 ) SELECT * FROM TEMP2五、多SQL存储(SELECT MANY SQL)-- 多sql结果按行存储 SELECT COUNT(1) AS table_count FROM Student where ID1 UNION ALL SELECT COUNT(1) AS table_count FROM Student where ID2; -- 多sql结果按列存储 SELECT (SELECT COUNT(1) AS table_count FROM Student where ID1)as count1, (SELECT COUNT(1) AS table_count FROM Student where ID2)as count2;六、逗号拼接STUFF【数据表】SchoolDataid 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 11 GROUP BY teachername;【模板】大哥找小弟逗号拼接提示表名是同一个然后将大哥和小弟替换为你想查的字段即可。SELECT 大哥, COUNT(*), STUFF( (SELECT , 小弟 FROM 表名 AS t2 WHERE 11 AND t2.大哥 t1.大哥 FOR XML PATH()), 1, 1, ) AS Alllist FROM 表名 AS t1 WHERE 11 GROUP BY 大哥 ORDER BY 大哥示例效果大哥是sumvalue小弟是number【模板】拼接一列字段--逗号拼接 SELECT STUFF(( SELECT , 单独的字段名 FROM 表名 FOR XML PATH()), 1, 1, ) AS T七、列的计算和作差SELECT score1, score2, (score1 - score2) AS scoreDiff, -- 差值 ROUND(score1, 3) AS score1_3, -- 保留3位小数 (ROUND(score1, 3) ROUND(score2, 3)) AS Diff_3 -- score1保留3位小数加score2保留3位小数 FROM table八、分组查询含窗口函数C#高级数据库中使用SQL作分组处理01简单分组-CSDN博客C#高级数据库中使用SQL作分组处理2WITH ROLLUP关键字_sql with rollup-CSDN博客C#高级数据库中使用SQL作分组处理3ROW_NUMBER() 窗口函数_c# row函数-CSDN博客C#高级数据库中使用SQL作分组处理4LAG() 偏移函数-CSDN博客C#高级数据库中使用SQL作分组处理5Rank() 排名函数-CSDN博客九、递归查询【需求】查找 AmoebaID4408180d-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十、表的自连接同比环比、层级查询假设表有字段ID Time Score可以用自连接写法展示以下数据ID 时间 分数 上次分数 差值已知一天一次考试SELECT t1.ID, t1.Time, t1.Score, t2.Score AS 上次分数, t1.Score - ISNULL(t2.Score, 0) AS 差值 FROM Score t1 LEFT JOIN Score t2 ON t1.ID t2.ID AND t2.Time DATEADD(DAY, -1, t1.Time) -- SQL Server: 当前时间减1天 ORDER BY t1.ID, t1.Time;扩展还可以应用于同比环比根据日期联表例如今天和昨天、层级查询根据关联字段联表例如查用户和它上司十二、事务阻塞的处理如果查询某表select * from a,要加nolock才查得出来可以断定99%的可能是此表存在事务阻塞的情况当业务出现事务阻塞、表无法写入时可查询锁表的会话 ID然后使用 KILL 命令强制终止会话使事务回滚并释放锁资源快速恢复业务。-- 第一步查出会话ID SELECT tl.request_session_id AS 会话ID, o.name AS 表名, t.text AS 执行的SQL FROM sys.dm_tran_locks tl INNER JOIN sys.objects o ON tl.resource_associated_entity_id o.object_id LEFT JOIN sys.sysprocesses p ON tl.request_session_id p.spid CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) t WHERE tl.resource_type OBJECT AND o.name 表名 -- 填写你的表名 -- 第二步强制终止该会话ID强制将该会话未执行完的事务回滚 KILL 会话ID