带有 HAVING 子句的 SQL 查询的 COUNT 结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3770093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COUNT results from SQL Query with a HAVING clause
提问by D.S.
Are you able to use COUNT in a query with a HAVING clause so that the COUNT returns the number of rows? When I try, Im getting the count of the number of times the ID shows up in the table. Here is the query:
您能否在带有 HAVING 子句的查询中使用 COUNT，以便 COUNT 返回行数？当我尝试时，我得到了 ID 出现在表中的次数。这是查询：
SELECT col_appid, min(col_payment_issued_date) as PayDate
FROM tbl_ui_paymentstubs
WHERE isnull(col_payment_amount,0) > 0
GROUP BY col_appid
HAVING min(col_payment_issued_date) >= '09/01/2010' and min(col_payment_issued_date) <= '09/30/2010'
I get back 6 rows, which is fine, but i'd like to just get back the number 6.
我取回 6 行，这很好，但我只想取回数字 6。
I found I could do it this way, but was wondering if there was another, more elegant way:
我发现我可以这样做，但想知道是否有另一种更优雅的方式：
WITH Claims_CTE(AppID, PayDate) as
(
SELECT col_appid, min(col_payment_issued_date) as PayDate
FROM tbl_ui_paymentstubs
WHERE isnull(col_payment_amount,0) > 0
GROUP BY col_appid
HAVING min(col_payment_issued_date) >= '09/01/2010' and min(col_payment_issued_date) <= '09/30/2010'
)
SELECT count(AppID) as Amount from Claims_CTE
`
`
回答by bdukes
Using COUNTwith a GROUP BYclause will provide a count for each group. If you want the count of the number of groups, it will have to be a separate query (like your CTE example).
使用COUNT带有GROUP BY条款将为每个组的计数。如果您想要组数的计数，则必须是一个单独的查询（如您的 CTE 示例）。
I would just use a simple subquery, instead of the CTE:
我只会使用一个简单的子查询，而不是 CTE：
SELECT COUNT(*) FROM
(SELECT col_appid, min(col_payment_issued_date) as PayDate
FROM tbl_ui_paymentstubs
WHERE isnull(col_payment_amount,0) > 0
GROUP BY col_appid
HAVING
min(col_payment_issued_date) >= '09/01/2010'
and min(col_payment_issued_date) <= '09/30/2010') Claims
回答by a'r
You can also use a sub-query.
您还可以使用子查询。
SELECT count(*) as Amount
FROM (
SELECT col_appid FROM tbl_ui_paymentstubs
WHERE isnull(col_payment_amount,0) > 0
GROUP BY col_appid
HAVING min(col_payment_issued_date) BETWEEN '09/01/2010' AND '09/30/2010'
) Claims
回答by ErikE
Assuming you have a table with the distinct list of col_appid values called App, this query also works and may be better performance, too:
假设您有一个包含名为 App 的 col_appid 值的不同列表的表，此查询也可以工作并且性能可能更好：
SELECT Count(*)
FROM
App A
CROSS APPLY (
SELECT TOP 1 col_payment_issued_date
FROM tbl_ui_paymentstubs P
WHERE
P.col_payment_amount > 0
AND A.col_appid = P.col_appid
ORDER BY col_payment_issued_date
) X
WHERE
X.col_payment_issued_date >= '09/01/2010'
AND X.col_payment_issued_date < '10/01/2010'
If there is no App table you can substitute (SELECT DISTINCT col_appid FROM tbl_ui_paymentstubs) Abut that will not perform as well. It could still be a contender compared to the other queries given.
如果没有 App 表，您可以替换，(SELECT DISTINCT col_appid FROM tbl_ui_paymentstubs) A但效果不佳。与给出的其他查询相比，它仍然可能是一个竞争者。
Other notes:
其他注意事项：
You don't need to do
isnull(column, 0) > 0becausecolumn > 0already excludes NULLs.@ar's and @bdukes' queries don't need anything in the inner SELECT clause, they can just be SELECT 1 which may be a performance improvement (nothing else changes)
I hope there's a constraint on col_payment_issued_date so that values do not have a time portion such as 11:23 AM, otherwise your BETWEEN clause will eventually not pull the correct data for the entire month.
您不需要这样做，
isnull(column, 0) > 0因为column > 0已经排除了 NULL。@ar 和 @bdukes 的查询不需要内部 SELECT 子句中的任何内容，它们可以是 SELECT 1 这可能是性能改进（没有其他变化）
我希望 col_payment_issued_date 有一个限制，这样值就没有时间部分，例如 11:23 AM，否则您的 BETWEEN 子句最终将无法提取整个月的正确数据。
Update
更新
- For what it's worth, the date format '20100901' will work everywhere, with any language or DATEFIRST setting. I encourage you to get in the habit of using it. Other formats such as '09/01/2010' or '2010/09/01' and so on can get the month and the day mixed up.
- 就其价值而言，日期格式“20100901”将适用于任何语言或 DATEFIRST 设置的任何地方。我鼓励你养成使用它的习惯。其他格式，例如“09/01/2010”或“2010/09/01”等，可能会混淆月份和日期。
@DScott said:
@DScott 说：
There is an tbl_Application, but in this instance is not used. I could join to it, but im just counting payments for this query so it is not required.
有一个 tbl_Application，但在本例中没有使用。我可以加入它，但我只是计算这个查询的付款，所以它不是必需的。
Would you mind trying my query and giving me feedback on its performance compared to the other methods? I am hoping that even with the extra join in the query, it performs pretty well.
您介意尝试我的查询并就其与其他方法相比的性能提供反馈吗？我希望即使在查询中加入额外的连接，它也能很好地执行。
