三种方式:
语法格式:
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)
语法格式:
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
语法格式:
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
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。 原因: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 子句中