sql月度分组_SQL按月分组

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: