如何在db2中使用group by子句进行COUNT（*）？

问题描述：

我想知道这个查询将返回多少行：

I want to know how many rows are going to be returned by this query:

SELECT 
  MONTH(DHSTMP), SUM(DHDLDY), SUM(DHBUDS), YEAR(DHSTMP) 
FROM 
  DSHDAY 
WHERE 
  DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59' 
GROUP BY 
  YEAR(DHSTMP), MONTH(DHSTMP) ORDER BY YEAR(DHSTMP), MONTH(DHSTMP)

所以我试过：

SELECT 
  COUNT(*) 
FROM 
  DSHDAY 
WHERE 
  DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59' 
GROUP BY 
  YEAR(DHSTMP), MONTH(DHSTMP)

但是它告诉我每组的行数。

But it tells me how many rows for each group.

如何获取第一个查询返回的行数？

How can I get the number of rows that will be returned for the first query?

答

这应该是正常的。但它不是性能。

（如果你只对计数感兴趣，你可能不想计算汇总和排序）

This should work. but it's not performant.
(if you're only interested in the count you probably don't want to calculate sum aggregation and ordering)

SELECT COUNT(*)
FROM (
  SELECT 
    MONTH(DHSTMP), 
    SUM(DHDLDY), 
    SUM(DHBUDS), 
    YEAR(DHSTMP) 
  FROM DSHDAY 
  WHERE DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59' 
  GROUP BY 
    YEAR(DHSTMP), 
    MONTH(DHSTMP) 
  ORDER BY 
    YEAR(DHSTMP), 
    MONTH(DHSTMP)
) i

尝试这个

Try this

SELECT COUNT( DISTINCT i.month_year )
FROM (
  SELECT 
    CONCAT( MONTH(DHSTMP), YEAR(DHSTMP) ) AS month_year
  FROM DSHDAY 
  WHERE DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59' 
) i

变化（在回答迈克的评论）

Variation (in answer to Mike's comment)

SELECT COUNT(*)
FROM (
  SELECT 0
  FROM DSHDAY 
  WHERE DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59' 
  GROUP BY 
    YEAR(DHSTMP), 
    MONTH(DHSTMP) 
) i

如何在db2中使用group by子句进行COUNT（*）？

相关推荐