代码之家 › 专栏 › 技术社区 › D.S.

使用HAVING子句统计SQL查询的结果

greatest-n-per-group count sql-server sql

D.S. · 技术社区 · 14 年前

您是否能够在带有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'

我回到6排,这很好,但我只想回到6排。

我发现我可以这样做,但我想知道是否有其他更优雅的方式:

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

3 回复 | 直到 14 年前

momo 14 年前

使用 COUNT 用一个 GROUP BY

我只使用一个简单的子查询,而不是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

a'r 14 年前

也可以使用子查询。

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

momo 14 年前

假设您有一个表,其中包含名为App的col\u 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'

如果没有应用程序表,则可以替换 (SELECT DISTINCT col_appid FROM tbl_ui_paymentstubs) A 但这不会有好的表现。与给出的其他查询相比,它仍然是一个竞争者。

其他注意事项:

isnull(column, 0) > 0 因为 column > 0
@ar和@bdukes的查询不需要内部SELECT子句中的任何内容,它们可以是SELECT 1,这可能会提高性能(没有其他更改)
我希望对col\u payment\u issued\u date有一个限制,这样值就没有时间部分,比如上午11:23,否则您的BETWEEN子句最终将无法获取整个月的正确数据。

值得一提的是,日期格式“20100901”适用于任何语言或日期优先设置。我鼓励你养成使用它的习惯。其他格式,如“09/01/2010”或“2010/09/01”等,可能会混淆月份和日期。

@德斯科特说:

有一个tbl\ U应用程序,但在本例中不使用。我可以加入它,但我只是计算这个查询付款,所以它不是必需的。

您介意尝试一下我的查询,并给我反馈它与其他方法相比的性能吗?我希望即使查询中有额外的连接,它也能运行得很好。

Chris Catignani Sagar Barapatre 4 年前

使用下面的查询计算Oracle中的重复记录。

SELECT Column1 , count(*) Num
FROM TABLE_NAME whe   
GROUP BY Column1 
HAVING count(*) > 1  
ORDER BY num desc;