sql 常用语法

1、with as 递归查询

        通过UNION ALL 连接部分。通过连接自身whit as 创建的表达式，它的连接条件就是递归的条件。可以从根节点往下查找，从子节点往父节点查找。只需要颠倒一下连接条件。例如代码中条件改为t.ID = c.ParentId即可

with tree as(
    --0 as Level 定义树的层级,从0开始
    select *,0 as Level
    from ClassUnis
    where ParentId is null
    union all
    --t.Level + 1每递归一次层级递增
    select c.*,t.Level + 1
    from ClassUnis c,tree t
    where c.ParentId = t.ID
    --from ClassUnis c inner join tree t on c.ParentId = t.ID
)
select * from tree where Author not like'%/%'

2、分页查询

DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 10

SELECT *
FROM F1_WorkflowApplications
ORDER BY CreationTime
OFFSET (@PageNumber - 1) * @RowspPage ROWS
FETCH NEXT @RowspPage ROWS ONLY;

3、分组统计并合计总数及排序

SELECT isnull([MsgType],'总数'), COUNT(*) AS Total FROM [dbo].[BusinessMessages]
Where [CreationTime]>='2024-01-01'
GROUP BY [MsgType] 
WITH ROLLUP 
Order by [MsgType] desc

WITH ROLLUP  和 WITH cube

ROLLUP和CUBE的区别就是： ROLLUP 只会去统计group by 后面的第一个字段每个分组的小计和第一个字段的总计，而CUBE 统计group by 后面的每一个字段分组的小计和第一个字段的总计

以下举例可进行对比：

SELECT [MsgType],ReceiveUserId, COUNT(*) AS Total FROM [dbo].[BusinessMessages]
Where [CreationTime]>='2024-01-01'
GROUP BY [MsgType],ReceiveUserId
with rollup
Order by [MsgType] desc

SELECT [MsgType],ReceiveUserId, COUNT(*) AS Total FROM [dbo].[BusinessMessages]
Where [CreationTime]>='2024-01-01'
GROUP BY [MsgType] ,ReceiveUserId
WITH cube
Order by [MsgType] desc,ReceiveUserId desc

GROUPING SETS

说明：GROUPING SETS 子句允许你指定多个GROUP BY选项，可以通过一条SELECT语句实现复杂繁琐的多条SELECT语句的查询，并且更加的高效。

GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集。

GROUPING SETS 可以生成等效于由简单 GROUP BY、ROLLUP 或 CUBE 操作生成的结果。

举例：

SELECT [MsgType],ReceiveUserId,AppID, COUNT(*) AS Total,sum(cast(ReadTag as int)) as rr FROM [dbo].[BusinessMessages]
Where [CreationTime]>='2024-01-01'
GROUP BY GROUPING SETS(
([MsgType], ReceiveUserId,AppID),
([MsgType], ReceiveUserId),
([MsgType])
)