select convert(varchar(10),Cre_Dat,120) as 日期,count(*) as 记录条数,
sum(In_Coin) as In_Coin,sum(Out_Coin) as Out_Coin
from 你的表
where month(Cre_Dat)=month(getdate()) --當前月份
group by convert(varchar(10),Cre_Dat,120)
sum(In_Coin) as In_Coin,sum(Out_Coin) as Out_Coin
from 你的表
where month(Cre_Dat)=month(getdate()) --當前月份
group by convert(varchar(10),Cre_Dat,120)
解决方案 »
- 数据库连接问题!windows 登陆报18456,sa可以登陆
- 求:解决数据库交叉表的问题
- 高分求教:请问SQLServer如何进行跨数据库操作的回滚?
- 查询分析器中,还原数据库的问题,急!
- sql 2000个人版安装不了
- 如何使用T_SQL语言设置Query time_out 属性。
- Sql语句中数据类型转换的问题,Int型转为str时为何增加了空格?
- 这个简单的过程有何问题?
- 像我这样的,在上海或北京能混到哪种程度
- SQL SERVER是不是只在局域网内才能互相看见,它能不能传透路由器?
- 为什么存储过程的出错控制起不了作用?
- 关于不在同一域的两台sqlserver之间的发布和订阅的讨论???
--返回 2004-04-02
像这样:日期 In_Coin Out_Coin 当天记录数
1 1500 1200 300
2 0 0 0
3 2000 1500 500
4 ……
create function f_qry(@年月 varchar(6))
returns table
as
return(
select a.日期,记录条数=isnull(记录条数,0)
,In_Coin=isnull(In_Coin,0),Out_Coin=isnull(Out_Coin,0)
from(
select 日期=convert(varchar(10),dateadd(day,a.id+b.id,@年月+'01'),120)
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
) a,(
select id=0 union all select 6
union all select id=12 union all select 18
union all select id=24 union all select 30
) b
)a left join(
select 日期=convert(varchar(10),Cre_Dat,120)
,记录条数=count(*)
,In_Coin=sum(In_Coin),Out_Coin=sum(Out_Coin)
from 表
group by convert(char(10),Cre_Dat,120)
)b on a.日期=b.日期
where datediff(month,日期,@年月+'01')=0
)
go
--调用示例
select * from f_qry('200302')
returns table
as
return(
select 日期=a.id+1
,In_Coin=isnull(In_Coin,0)
,Out_Coin=isnull(Out_Coin,0)
,当天记录数=isnull(记录条数,0)
from(
select id,日期=convert(varchar(10),dateadd(day,a.id+b.id,@年月+'01'),120)
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
) a,(
select id=0 union all select 6
union all select id=12 union all select 18
union all select id=24 union all select 30
) b
)a left join(
select 日期=convert(varchar(10),Cre_Dat,120)
,记录条数=count(*)
,In_Coin=sum(In_Coin),Out_Coin=sum(Out_Coin)
from 表
group by convert(char(10),Cre_Dat,120)
)b on a.日期=b.日期
where datediff(month,日期,@年月+'01')=0
)
go
select A.dt,isnull(B.记录条数,0) as 记录条数 , isnull(B.In_Coin,0) as In_Coin, isnull(B.Out_Coin,0) as Out_Coin
from #date A
left join
(select convert(varchar(10),Cre_Dat,120) as 日期,count(*) as 记录条数,
sum(In_Coin) as In_Coin,sum(Out_Coin) as Out_Coin
from 你的表
where month(Cre_Dat)=month(getdate()) --當前月份
group by convert(varchar(10),Cre_Dat,120)
) as
B on B.日期=A.convert(varchar(10),dt,120)
declare @sql varchar(800)
select @sql='exec SP_PAS_DBQB_END ''20040401'',''SJ'''
exec msdb..sp_add_jobstep @job_name='结束',
@step_name = '20040401',
@subsystem = 'TSQL',
@database_name='MYDB',
@command = @sql,
@retry_attempts = 0--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '结束',
@name = '调度',
@freq_type=1,
@freq_subday_type=0x1,
@active_start_date =20040401,
@active_start_time =112140
-- 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver
@job_name = '结束' ,
@server_name = N'(local)'
set @ = '2004-4-6'
select min(dateadd(day,n.i,dateadd(month,datediff(month,0,@),0)))
,sum(In_Coin),sum(Out_Coin),count(*)from T right join(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31
) N
on datediff(day,dateadd(day,n.i,dateadd(month,datediff(month,0,@),0)),T.cre_dat)=0
where datediff(month,dateadd(day,n.i,dateadd(month,datediff(month,0,@),0)),@)=0
group by datediff(day,0,dateadd(day,n.i,dateadd(month,datediff(month,0,@),0)))
(1)您的查询有问题,选2月份查到3月一号的数据了。
(2)月份没有用,每个月的数据都一样。~~