！[在此输入图像描述][1]
我想在选择中添加一列"；所有行号"；is all行已获取(不使用子查询(。
可以使用任何数据。
这是我的例子，所以每个人都明白我需要什么。
select *, "all line number"
from SysUserInSubRoleRes
group by SubRoleId
having count(SubRoleId) > 2
order by SubRoleId desc
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY'
Value default:
SubRoleId
--------
v1
v2
v3
v1
v2
v2
v1
Result Value
add column alllinenumber
SubRoleID | AllLineNumber
v1 | 3
v2 | 3
您的预期输出不能是查询的结果，因为子类1具有alllinenumber=1和2均未通过>2 的计数测试
你可能正在寻找类似的东西
drop table t
go
create table t(subroleid int)
go
insert into t values (1),(2),(2),(2)
select subroleid, count(*) as "all line number"
from t
group by SubRoleId
having count(SubRoleId) > 2
order by SubRoleId desc
subroleid all line number
----------- ---------------
2 3
但如果没有样本数据和预期输出，就很难确定。
编辑1。
drop table t
go
create table t(subroleid varchar(2))
go
insert into t values ('v1'),('v2'),('v3')
select t.subroleid, cnt "all line number"
from t
cross join (select count(*) cnt from t) s
where cnt > 2
order by SubRoleId
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY
subroleid all line number
--------- ---------------
v1 3
v2 3
如果你想要行号，那么你可以很容易地生成
select SubRoleId, ROW_NUMBER() OVER(ORDER BY SubRoleId DESC) AS [all line number]
from SysUserInSubRoleRes
group by SubRoleId
having count(SubRoleId) > 2
order by [all line number]
SELECT COUNT(SUBROLEID) AS [ALL LINE NUMBER]
FROM SYSUSERINSUBROLERES
GROUP BY SUBROLEID
HAVING COUNT(SUBROLEID) > 2
ORDER BY SUBROLEID DESC
希望此查询适用于您的案例