SQL按月分组 [英] SQL Results group by month

问题描述

我试图在一个滚动的12个月期间返回一些结果，例如：

  MONTH IN OUT 
 1月210 191 
 2月200 111 
 3月132 141 
 4月112 141 
 5月191 188 
等... 
  
 
如何在日期范围内分散结果，填写月份名称的第一列？ 
 
 
在MSSQL中，它会是这样的：
  SELECT COUNT（problem.problem_type ='IN'）AS IN， 
 COUNT（problem.problem_type ='OUT'）AS OUT，
 DATEPART（year，DateTime）as Year，
 DATEPART（month，DateTime）as Month 
 FROM problem 
 WHERE（DateTime> = dbo.FormatDateTime（'2010-01-01'））
 AND 
（DateTime< dbo.FormatDateTime（'2010-01-31'））
 GROUP BY DATEPART（year，DateTime），
 DATEPART（month，DateTime）; 
  
但这是针对Oracle数据库的，因此DATEPART和DateTime不可用。
 
 
 
我的问题表大致为： 
 
 
  problem_ID Problem_type IN_Date OUT_Date 
 1 IN 2010 -01-23 16：34：29.0 2010-02-29 13：06：28.0 
 2 2010-01-27 12：34：29.0 2010-01-29 12：01：28.0 
 3 OUT 2010-02-13 13：24：29.0 2010-09-29 15：04：28.0 
 4 OUT 2010-02-15 16：31：29.0 2010-07-29 11：03：28.0 
  
 
 
解决方案
使用：
  SELECT SUM（CASE WHEN p.problem_type ='IN'THEN 1 ELSE 0 END）as in，
 SUM（CASE WHEN p.problem_type ='OUT'THEN 1 ELSE 0 END）AS OUT，
 TO_CHAR（datetime，'YYYY'）AS year，
 TO_CHAR（datetime，'MM'）AS month 
 FROM PROBLEM p 
 WHERE p。 DateTime> = TO_DATE（ '2010-01-01'，'YYYY-MM-DD'）
 AND p.DateTime< TO_DATE（'2010-01-31'，'YYYY-MM-DD'）
 GROUP BY TO_CHAR（datetime，'YYYY'），TO_CHAR（datetime，'MM'）
  
 
 
 选择 
 
> 
 
> SUM（CASE WHEN p.problem_type ='IN'THEN 1 ELSE 0 END）as IN，
 SUM（CASE WHEN p.problem_type ='OUT'THEN 1 ELSE 0 END）AS OUT，
 TO_CHAR（ date'，'MM-YYYY'）AS mon_year 
 FROM PROBLEM p 
 WHERE p.DateTime> = TO_DATE（'2010-01-01'，'YYYY-MM-DD'）
 AND p.DateTime< TO_DATE（'2010-01-31'，'YYYY-MM-DD'）
 GROUP BY TO_CHAR（datetime，'MM-YYYY'）
  
参考： 
 
 
 
 
  TO_CHAR  
 
 
  TO_DATE  
 
 
 
I'm trying to return some results spread over a rolling 12 month period eg:
MONTH       IN   OUT
January    210    191
February   200    111
March      132    141
April      112    141
May        191    188 
etc...
How do I spread the results over a date range, populating the first column with the month name?

IN MSSQL it would be something like:
SELECT  COUNT(problem.problem_type = 'IN') AS IN, 
    COUNT(problem.problem_type = 'OUT') AS OUT, 
    DATEPART(year, DateTime) as Year,
    DATEPART(month, DateTime) as Month
FROM problem
WHERE   (DateTime >= dbo.FormatDateTime('2010-01-01')) 
    AND 
    (DateTime < dbo.FormatDateTime('2010-01-31'))
GROUP BY DATEPART(year, DateTime),
    DATEPART(month, DateTime);
But this is against an Oracle database so DATEPART and DateTime are not available.

My Problem table is roughly:
problem_ID Problem_type   IN_Date                     OUT_Date
   1           IN        2010-01-23 16:34:29.0       2010-02-29 13:06:28.0
   2           IN        2010-01-27 12:34:29.0       2010-01-29 12:01:28.0
   3           OUT       2010-02-13 13:24:29.0       2010-09-29 15:04:28.0
   4           OUT       2010-02-15 16:31:29.0       2010-07-29 11:03:28.0

解决方案
Use:
  SELECT SUM(CASE WHEN p.problem_type = 'IN' THEN 1 ELSE 0 END) AS IN, 
         SUM(CASE WHEN p.problem_type = 'OUT' THEN 1 ELSE 0 END) AS OUT, 
         TO_CHAR(datetime, 'YYYY') AS year,
         TO_CHAR(datetime, 'MM') AS month
    FROM PROBLEM p
   WHERE p.DateTime >= TO_DATE('2010-01-01', 'YYYY-MM-DD') 
     AND p.DateTime < TO_DATE('2010-01-31', 'YYYY-MM-DD') 
GROUP BY TO_CHAR(datetime, 'YYYY'), TO_CHAR(datetime, 'MM')
You could also use:
  SELECT SUM(CASE WHEN p.problem_type = 'IN' THEN 1 ELSE 0 END) AS IN, 
         SUM(CASE WHEN p.problem_type = 'OUT' THEN 1 ELSE 0 END) AS OUT, 
         TO_CHAR(datetime, 'MM-YYYY') AS mon_year
    FROM PROBLEM p
   WHERE p.DateTime >= TO_DATE('2010-01-01', 'YYYY-MM-DD') 
     AND p.DateTime < TO_DATE('2010-01-31', 'YYYY-MM-DD') 
GROUP BY TO_CHAR(datetime, 'MM-YYYY')
Reference:


TO_CHAR
TO_DATE


                        
这篇关于SQL按月分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！