复杂SQL分页查询

Question

我正在使用this问题的解决方案对数据进行分页。

我现在需要使用这个解决方案来进行更复杂的查询。即。括号中的SELECT具有联接和聚合函数。

这就是我用来作为参考的解决方案：

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

我需要将这个查询合并到上面的解决方案中：

SELECT users.indicator, COUNT(*) as 'queries' FROM queries
INNER JOIN calls ON queries.call_id = calls.id
INNER JOIN users ON calls.user_id = users.id
WHERE queries.isresolved=0 AND users.indicator='ind1'
GROUP BY users.indicator ORDER BY queries DESC

我怎样才能做到这一点？到目前为止，我已经通过删除ORDER BY queries DESC部件并将其放入行ROW_NUMBER() OVER (ORDER BY ...) AS RowNum来实现它，但是当我这样做时，它不允许我按该列进行排序(“无效列名‘查询’”)。

我需要做些什么才能让它按这个专栏来订购呢？

编辑：使用Server 2008

Answer 1

尝试按计数(*) DESC排序。它在MySQL上工作..。不确定Server 2008

Answer 2

我认为为count(*)列查询您的别名，然后像这样使用

SELECT users.indicator, COUNT(*) as 'queries' FROM queries
INNER JOIN calls ON queries.call_id = calls.id
INNER JOIN users ON calls.user_id = users.id
WHERE queries.isresolved=0 AND users.indicator='ind1'
GROUP BY users.indicator ORDER BY COUNT(*) DESC

http://oops-solution.blogspot.com/2011/11/string-handling-in-javascript.html