获取聚合查询中扫描的记录数

发布于 2024-09-03 06:19:46 字数 382 浏览 8 评论 0原文

我的应用程序使用一般形式执行许多查询：

SELECT <aggregate-functions>
FROM <table-name>
WHERE <where-clause>
GROUP BY <group-by column list>

我想知道有多少记录对聚合结果集有贡献（换句话说，有多少记录与 WHERE 子句中的条件匹配），由于以下原因而隐藏了一些内容SELECT 子句和 GROUP-BY 子句中的聚合函数。我知道我可以使用相同的 WHERE 子句执行简单的 SELECT COUNT(*) 来获取该数字，但如果可能的话，我想避免第二次查询。是否有 SQL Server 特性/函数/等可以在无需其他查询的情况下生成该值？

原文

My application performs a number of queries with the general form:

SELECT <aggregate-functions>
FROM <table-name>
WHERE <where-clause>
GROUP BY <group-by column list>

I would like to know how many records contributed to the aggregate result set (in other words, how many records matched the conditions in the WHERE clause), something that is hidden because of both both the aggregate functions in the SELECT clause and the GROUP-BY clause. I know that I could do a simple SELECT COUNT(*) using the same WHERE clause to get that number, but I would like to avoid a second query if at all possible. Is there a SQL Server feature/function/etc that will produce that value without another query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

回首观望 2024-09-10 06:19:47

一个简单的解决方案是子选择：

Select ...
    , ( Select Count(*) 
        From Table 
        Where ...) As TotalCount
From Table
Where ...
Group By ...

如果您使用的是 SQL Server 2005 或更高版本，您可以执行如下操作：

With RankedItems As
    (
    Select Col1
        , Row_Number() Over ( Order By Col1 Asc, Col2 Asc... ) As Num
        , Row_Number() Over ( Order By Col1 Desc, Col2 Desc ) As RevNum
    From Table
    Where ...
    )
Select Col1, Min(Num + RevNum - 1) As TotalCount
From RankedItems
Group By Col1

A simple solution would be a subselect:

Select ...
    , ( Select Count(*) 
        From Table 
        Where ...) As TotalCount
From Table
Where ...
Group By ...

If you are using SQL Server 2005 or later, you can do something like the following:

With RankedItems As
    (
    Select Col1
        , Row_Number() Over ( Order By Col1 Asc, Col2 Asc... ) As Num
        , Row_Number() Over ( Order By Col1 Desc, Col2 Desc ) As RevNum
    From Table
    Where ...
    )
Select Col1, Min(Num + RevNum - 1) As TotalCount
From RankedItems
Group By Col1

回复收藏 0 原文

跨年 2024-09-10 06:19:47

您可以添加涉及聚合的任意值的计数，甚至可以这样做

SELECT  x.name, 
        y.name, 
        z.name, 
        AVG(x.tacos) AS average_tacos, 
        SUM(1) AS aggregated_row_count
FROM ....
WHERE ...
GROUP BY x.name, y.name, z.name

You can add in a count for some arbitrary value that's involved the aggregation, or even do

SELECT  x.name, 
        y.name, 
        z.name, 
        AVG(x.tacos) AS average_tacos, 
        SUM(1) AS aggregated_row_count
FROM ....
WHERE ...
GROUP BY x.name, y.name, z.name

回复收藏 0 原文

~没有更多了~