我可以从 sql server 中的一个 sql 查询中获取 count() 和行吗？

Question

我想获取结果总数和某些查询的前 n 行 - 是否可能 在一份声明中？

我期望的结果是：

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

提前谢谢您！

Answer 1

像这样：

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

编辑：在投反对票和计数/结束答案后。对我的 2 个表进行比较，

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

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

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.

Answer 2

怎么样

SELECT COUNT(*) OVER() AS C, COLUMN1, COLUMN2
FROM TABLE  

关于CROSS JOIN查询
在复杂的 INSERT/DELETE 环境中，交叉联接将返回不正确的行计数。

从多个连接尝试此操作
连接 1

set nocount on;
drop table dbo.test_table;
GO
create table dbo.test_table
(
    id_field uniqueidentifier not null default(newid()),
    filler char(2000) not null default('a')
);
GO
create unique clustered index idx_id_fld on dbo.test_table(id_field);
GO
while 1 = 1
insert into dbo.test_table default values;

连接 2

select T2.cnt, T1.id_field, T1.filler
from dbo.test_table T1
cross join (select COUNT(*) as cnt from dbo.test_table) T2

select T2.cnt, T1.id_field, T1.filler
from dbo.test_table T1
cross join (select COUNT(*) as cnt from dbo.test_table) T2

select T2.cnt, T1.id_field, T1.filler
from dbo.test_table T1
cross join (select COUNT(*) as cnt from dbo.test_table) T2

每次记录数 (@@ROWCOUNT) 与 T2.cnt 不同

在 COUNT(*) OVER() 的情况下，只有一个表扫描，并且 @@ROWCOUNT 始终与 T2.cnt 相同

关于查询计划 - SQL 2005 SP3 似乎很多在执行 COUNT(*) OVER() 方面比 SQL 2008 R2 弱。最重要的是，它错误地报告了查询成本（我从未想过子查询的成本可能超过整个查询的 100%）。

在很多情况下，COUNT(*) OVER() 的成本在 CROSS JOIN 的 50-75% 之间。

交叉连接的最佳情况是如果有一个非常狭窄的索引来进行计数。这样就会对数据进行聚集索引扫描+对计数进行索引扫描。

与往常一样，最好是衡量、衡量、衡量并做出您愿意接受的妥协。

Answer 3

您可以使用CROSS JOIN和CTE来做到这一点，但它不是很有效：

WITH Rows_CTE AS
(
    SELECT Column1, Column2
    FROM Table
    WHERE (...)
)
SELECT c.Cnt, r.Column1, r.Column2
FROM Rows_CTE r
CROSS JOIN (SELECT COUNT(*) AS Cnt FROM Rows_CTE) c

我认为获得您想要的结果的更好方法是使用单个查询但多个结果集，您可以使用COMPUTE来完成：

SELECT Column1, Column2
FROM Table
WHERE (...)
COMPUTE COUNT([Column1])

Answer 4

尝试一下这个查询：

select ColumnId,Descr,(select COUNT(*) from ColumnSetUp)as c
from ColumnSetUp
group by ColumnId,Descr