SQL Server:如何在不分组的情况下使用 COUNT 子句? [英] SQL Server: How can I use the COUNT clause without GROUPing?

问题描述

在给定一组约束的情况下，我希望从查询中得到两件事:

I'm looking get two things from a query, given a set of contraints:

• 第一场比赛
• 匹配总数

我可以通过以下方式获得第一场比赛:

I can get the first match by:

SELECT TOP 1
    ID,
    ReportYear,
    Name,
    SignDate,
     ... 
FROM Table
WHERE
     ...
ORDER BY ... //I can put in here which one I want to take

然后我可以得到匹配计数，如果我使用

And then I can get the match count if I use

SELECT
    MIN(ID),
    MIN(ReportYear),
    MIN(Name),
    MIN(SignDate),
     ... ,
    COUNT(*) as MatchCount
FROM Table
WHERE
     ...
GROUP BY
     ??? // I don't really want any grouping

我真的想避免对所有结果进行分组和使用聚合函数.这个问题 SQL Server Select COUNT without using aggregate function或 group by 建议答案是

I really want to avoid both grouping and using an aggregate function on all my results. This question SQL Server Select COUNT without using aggregate function or group by suggests the answer would be

SELECT TOP 1
    ID,
    ReportYear,
    Name,
    SignDate,
     ... ,
    @@ROWCOUNT as MatchCount
FROM Table

这在没有 TOP 1 的情况下有效，但是当它在那里时，@@ROWCOUNT = 返回的行数，即 1.我怎样才能获得 COUNT(*) 的输出(无论在 where 子句之后剩下的)没有任何分组或需要聚合所有列?

This works without the TOP 1, but when it's in there, @@ROWCOUNT = number of rows returned, which is 1. How can I get essentially the output of COUNT(*) (whatever's left after the where clause) without any grouping or need to aggregate all the columns?

我不想做的是将这些重复两次，第一行一次，@@ROWCOUNT 一次.我没有找到一种可以正确使用 GROUP BY 的方法，因为我严格想要符合我的条件的项目数，并且我想要列如果我对它们进行分组就会丢弃这个数字 - 除非我误解了 GROUP BY.

What I don't want to do is repeat each of these twice, once for the first row and then again for the @@ROWCOUNT. I'm not finding a way I can properly use GROUP BY, because I strictly want the number of items that match my criteria, and I want columns that if I GROUPed them would throw this number off - unless I'm misunderstanding GROUP BY.

推荐答案

好吧，你可以使用OVER子句，它是一个窗口函数.

Well, you can use OVER clause, which is an window function.

SELECT  TOP (1)
    OrderID, CustID, EmpID,
    COUNT(*) OVER() AS MatchCount
FROM    Sales.Orders
WHERE   OrderID % 2 = 1
ORDER BY OrderID DESC 

这篇关于SQL Server:如何在不分组的情况下使用 COUNT 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！