如何从sql server中的一个sql查询中获取count（）和行？

问题描述

我想获取某个查询的结果和前n行的总数-一条语句中可能有
吗？

I'd like to get the total count of results and top n rows of some query - is it possiblein one statement?

I' d预期结果为：

I'd expect the results as:

count(..) column1        column2
125         some_value   some_value
125         some_value   some_value

谢谢！

推荐答案

像这样：

SELECT TOP 100 --optional
    MC.Cnt, M.Column1, M.Column2
FROM
    myTable M
    CROSS JOIN
    (SELECT COUNT(*) AS Cnt FROM myTable) MC

编辑：downvote和COUNT / OVER答案后。比较我的2个表

After downvote and COUNT/OVER answer. A comparison on 2 tables of mine

您可以看到我的CROSS JOIN /简单聚合与COUNT /空ORDER BY子句之间的巨大差异

You can see a huge difference between my CROSS JOIN/simple aggregate and a COUNT/empty ORDER BY clause

SELECT COUNT(*) OVER() AS C, key1col, key2col
FROM myTable

(24717 row(s) affected)

Table 'Worktable'. Scan count 3, logical reads 49865, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'myTable'. Scan count 1, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

StmtText
  |--Nested Loops(Inner Join)
       |--Table Spool
       |    |--Segment
       |         |--Index Scan(OBJECT:([MyDB].[dbo].[myTable].[IX_useful]))
       |--Nested Loops(Inner Join, WHERE:((1)))
            |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1005],0)))
            |    |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
            |         |--Table Spool
            |--Table Spool

SELECT
    MC.Cnt, M.key1col, M.key2col
FROM
    myTable M
    CROSS JOIN
    (SELECT COUNT(*) AS Cnt FROM myTable) MC

(24717 row(s) affected)

Table 'myTable'. Scan count 2, logical reads 154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


StmtText
  |--Nested Loops(Inner Join)
       |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1009],0)))
       |    |--Stream Aggregate(DEFINE:([Expr1009]=Count(*)))
       |         |--Index Scan(OBJECT:([MyDB].[dbo].[myTable].[IX_useful]))
       |--Index Scan(OBJECT:([MyDB].[dbo].[myTable].[IX_useful] AS [M]))

我在具有570k行的表上重复了此操作这是IO

I've repeated this on a table with 570k rows and here is the IO

Table 'Worktable'. Scan count 3, logical reads 1535456, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'myTable'. Scan count 1, logical reads 2929, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table 'myTable'. Scan count 34, logical reads 6438, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

这篇关于如何从sql server中的一个sql查询中获取count（）和行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！