Question

ORACLE 写一条Sql语句查出年份,1月,2月,3月....12月的订单总数列表

表结构：
CREATE TABLE [dbo].[Exam](
[S_date] [datetime] NOT NULL,
[Order_Id] [varchar](50) NOT NULL,
[Product_Id] [varchar](50) NOT NULL,
[Amt] [numeric](18, 0) NOT NULL
) ON [PRIMARY]

select y,sum(c1) as m1,sum(c2) as m2,sum(c3) as m3,sum(c4) as m4,sum(c5) as m5,sum(c6) as m6,
sum(c7) as m7,sum(c8) as m8,sum(c9) as m9,sum(c10) as m10,sum(c11) as m11,sum(c12) as m12
from
(
select
y,
case m when 1 then c else 0 end as c1,
case m when 2 then c else 0 end as c2,
case m when 3 then c else 0 end as c3,
case m when 4 then c else 0 end as c4,
case m when 5 then c else 0 end as c5,
case m when 6 then c else 0 end as c6,
case m when 7 then c else 0 end as c7,
case m when 8 then c else 0 end as c8,
case m when 9 then c else 0 end as c9,
case m when 10 then c else 0 end as c10,
case m when 11 then c else 0 end as c11,
case m when 12 then c else 0 end as c12
from
(
select y,m,count(s_date) as c from
(
select datepart(year,convert(DateTime,s_date)) as y,
datepart(month,convert(DateTime,s_date)) as m ,
s_date from exam
) as T1
group by T1.y,T1.m
)
as T2
) as T3
group by T3.y（这是sql server的)

• 你的回答被采纳后将获得：
• 系统奖励15（财富值+成长值）+难题奖励20（财富值+成长值）

Answer 1

select
y , sum(case when m =1 then 1 else 0 end ) as c1
,sum(case when m =2 then 1 else 0 end ) as c2
...
,sum(case when m =12 then 1 else 0 end ) as c12
from (
select
EXTRACT(year from s_date) as y
, EXTRACT(month from s_date) as m
from exam

) group by y

你试试，这个有一个限制，就是s_date必须是同一年的数据，如果跨年了，你就自己加条件吧。