select DateAdd(m,-9,getdate())
查询结果是9个月前的当前日期
select getdate()
查询结果是当前的日期假如我自己的表里查询出的结果如下:月份 数量
3 100
4 200
6 240
11 400现在我想查询最近10个月的情况
类似下面的效果月份 数量
4 200
5 0
6 240
7 0
8 0
9 0
10 0
11 400
12 0
1 0
sql 语句怎么写
查询结果是9个月前的当前日期
select getdate()
查询结果是当前的日期假如我自己的表里查询出的结果如下:月份 数量
3 100
4 200
6 240
11 400现在我想查询最近10个月的情况
类似下面的效果月份 数量
4 200
5 0
6 240
7 0
8 0
9 0
10 0
11 400
12 0
1 0
sql 语句怎么写
month(时间) as 月份,
sum(数量) as 数量
from tb
where datediff(m,时间,getdate())>=10
group by month(时间)
month(时间) as 月份,
sum(数量) as 数量
from tb
where datediff(m,时间,getdate())<=10
group by month(时间)
select 3,100
union all select 4,200
union all select 6,240
union all select 11,400select top 100 id=identity(int,1,1) into #tmp from syscolumns a,syscolumns b select
datepart(m,dateadd(m,-id,getdate())) as mon,
(case when qty is null then 0 else qty end) as qty
from #tmp left join m on id=mon
where datediff(m,dateadd(m,-id,getdate()),getdate())<=10drop table #tmp
drop table m
(case when qty is null then 0 else qty end) as qty
---急如果你表里面月份字段为空,就显示0,否则显示当前月的数量
identity(1,1)是自增那id=identity(int,1,1) 什么意思?
with c
as
(select cast(convert(varchar(8),getdate()+30,120)+'01' as datetime) as Col
union all
select dateadd(m,-1,Col) from c where dateadd(m,-1,Col)>=dateadd(m,-8,convert(varchar(8),getdate(),120)+'01'))select
month(c.Col) as 月份,sum(isnull(retailCount,0) )as retailCount
from c
left join SHOP_RETAIL s on convert(varchar(8),col,120)=convert(varchar(8),bizData,120)
group by month(c.Col)
order by month(c.Col) desc
select
bizMonth,
sum(retailCount) retailCount
from
BIZ_SHOP_RETAIL
where
bizData >= DateAdd(m,-9,getdate())
and bizData <= getdate()
group by bizMonth
order by bizMonth这个是我初始的SQL语句 查询结果类似下面的
月份 数量
3 100
4 200
6 240
11 400那我现在想要近10个月的数量情况
就像下面这样的效果
月份 数量
4 200
5 0
6 240
7 0
8 0
9 0
10 0
11 400
12 0
1 0
SQL语句怎么写?这就是我当初问的问题呀~~~~~~~~~~~~~~~~~