SQL Server使用常见问题

SQL Server使用常见问题

普通分页查询

三种方式：

1. Top Not IN 方式：查询靠前的数据较快
2. ROW_NUMBER() OVER()方式：查询靠后的数据速度比上一种较快，在老版本的SQL Server中最常使用
3. offset fetch next方式：速度优于前两者，限制Sql2012以上可以使用

Top Not IN 方式

语法格式：

select top 条数 *  from tablename
where Id not in (select top pageSize*(pageIndex-1)  Id from tablename)

示例：

SELECT TOP 2  * FROM Users
WHERE Id NOT IN (SELECT TOP 2 Id FROM Users)

ROW_NUMBER() OVER()方式

语法格式：

SELECT * FROM (SELECT *,
ROW_NUMBER() OVER(Order by Id ) AS RowNumber from tablename ) as b
where RowNumber between pageIndex-1*pageSize and pageIndex*pageSize

示例：

SELECT * FROM (SELECT *,
ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Users ) as b
where RowNumber BETWEEN 0 and 3

offset fetch next方式

语法格式：

select * from tablename
order by Id offset pageIndex row fetch next pageSize row only

示例：

select * from Users order by Id offset 2 row fetch next 5 row only

带GROUP BY子句查询

1.当存在GROUP BY子句时，查询结果中的列和排序条件中的列必须使用聚合函数或者作为分组条件，否则将报错：

选择列表中的列 'xxxx' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

示例：

SELECT 
bizId, contentId, sum(click) as click
from pushhotres with(nolock) 
where is_del=0 
AND publishtime >= '2022-03-15' 
AND publishtime <= '2022-04-07'
GROUP BY bizId,contentId
ORDER BY click DESC

2.当存在GROUP BY子句时，如果此时需要进行分页处理，分页查询和总数查询都需要特别处理。

（1）带GROUP BY子句的分页查询

当查询语句中带GROUP BY子句时，分页查询需要借助于临时表进行处理，否则很容易报错。
存在GROUP BY子句时，分页查询的大致语法：

WITH query AS (
SELECT ROW_NUMBER() OVER (ORDER BY 排序列) as __row_number__, * FROM (
SELECT TOP 100 PERCENT 查询列
from 表名 with(nolock)
where 条件
GROUP BY 分组列
ORDER BY 排序列) as TT
) SELECT * FROM query WHERE __row_number__ BETWEEN 当前页起始索引 AND 当前页结束索引 ORDER BY __row_number__

示例：

WITH query AS (
SELECT ROW_NUMBER() OVER (ORDER BY click DESC) as __row_number__, * FROM (
SELECT TOP 100 PERCENT 
bizId, contentId, sum(click) as click
from res with(nolock)
where is_del=0
AND publishtime >= '2022-03-15'
AND publishtime <= '2022-04-07'
GROUP BY bizId,contentId
ORDER BY click DESC) as TT
) SELECT * FROM query WHERE __row_number__ BETWEEN 1 AND 30 ORDER BY __row_number__

（2）带GROUP BY子句的总数查询

当查询语句中带GROUP BY子句时，查询总数需要使用一点技巧，不能直接使用select count()。
存在GROUP BY子句时，查询记录总数大致语法：

SELECT count(1) from
(
SELECT 1 as C from 表名 with(nolock)
where 条件
GROUP BY 分组列
) as TT

示例：

SELECT count(1) from
(
SELECT 1 as C from res with(nolock)
where is_del=0
AND publishtime >= '2022-03-15'
AND publishtime <= '2022-04-07'
GROUP BY bizId,contentId
) as TT

除法计算结果为0的处理

现象：当除数小于被除数时，除法运算结果不保留小数位，直接取整为0。
原因：SQL Server会自动根据除数与被除数的最大精度去对运算结果取精度，如果除数和被除数都是整数，所以得到的结果也是整数。
解决：只要除数与被除数之中有一个是小数，得到的结果也会是小数。手动给被除数加上0.00，即：

select 3/4    -- 结果：0
select 3.0/4  -- 结果：0.750000

【参考】
https://www.jianshu.com/p/d1ae74bda1c5 三种SQLServer分页查询语句笔记
https://blog.csdn.net/wh445306/article/details/118567103 sql 除法计算一直 为0原因及解决方案
https://www.cnblogs.com/printn/p/6725026.html 选择列表中的列……无效，因为该列没有包含在聚合函数或 GROUP BY 子句中