你能否在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
`