Create procedure 你的存贮过程名 @Date smalldatetime
As
Select 币别,类别,
(Select sum(发生额) from 表 where datediff(day,日期,@Date) = 0) as 比上日,
(Select sum(发生额) from 表 where datediff(month,日期,@Date) = 0 and 日期 <=@Date) as 比上月,
(Select sum(发生额) from 表 where datediff(year,日期,@Date) = 0 and 日期<=@Date) as 比上年
From 表
As
Select 币别,类别,
(Select sum(发生额) from 表 where datediff(day,日期,@Date) = 0) as 比上日,
(Select sum(发生额) from 表 where datediff(month,日期,@Date) = 0 and 日期 <=@Date) as 比上月,
(Select sum(发生额) from 表 where datediff(year,日期,@Date) = 0 and 日期<=@Date) as 比上年
From 表
Select a.日期,a.币别,a.类别,
(Select sum(发生额) from 表 where datediff(day,日期,a.日期) = 0) as 比上日,
(Select sum(发生额) from 表 where datediff(month,日期,a.日期) = 0 and 日期 <=a.日期) as 比上月,
(Select sum(发生额) from 表 where datediff(year,日期,a.日期) = 0 and 日期<=a.日期) as 比上年
From 表 a
where a.日期 = '2003-04-02'
(select sum(发生额) from 一个表 where datediff(day,日期,tem.日期)=0) 比上日,
(select sum(发生额) from 一个表 where datediff(month,日期,tem.日期)=0 and 日期<=tem.日期) 比上月,
(select sum(发生额) from 一个表 where datediff(year,日期,tem.日期)=0 and 日期<=tem.日期) 比上年
from 一个表 tem
create proc proc_1
@query_date varchar(10)
as
select 日期,币种,类别,sum(发生额)as 比上日 into #temp1 from tablename where 日期=@query_date group by 日期,币种,类别
select 币种,类别,sum(发生额) AS 比上月 into #temp2 from tablename where substring(日期,1,7)=substring(@query_date,1,7) group by 币种,类别select 币种,类别,sum(发生额)AS 比上年 into #temp3 from tablename where substring(日期,1,4)=substring(@query_date,1,4) group by 币种,类别
select 日期,#temp1.币种,#temp1.类别,比上日,比上月,比上年 from #temp1,#temp2,#temp3 where #temp1.币种=#temp2.币种 and #temp2.币种=#temp3.币种 and #temp1.类别=#temp2.类别 and #temp2.类别=#temp3.类别
go
select 日期,币种,类别,
(select sum(发生额) from tableName where datediff(day,日期,tem.日期)=0) 比上日,
(select sum(发生额) from tableName where datediff(month,日期,tem.日期)=0 and 日期<=tem.日期) 比上月,
(select sum(发生额) from tableName where datediff(year,日期,tem.日期)=0 and 日期<=tem.日期) 比上年
from tableName tem
@date_time datetime
as
select 日期,币种,类别,
(select sum(发生额) from tableName where datediff(day,日期,tem.日期)=0) 比上日,
(select sum(发生额) from tableName where datediff(month,日期,tem.日期)=0 and 日期<=tem.日期) 比上月,
(select sum(发生额) from tableName where datediff(year,日期,tem.日期)=0 and 日期<=tem.日期) 比上年
from tableName tem where 日期=@date_time执行:test '2003-04-02'
@date varchar(20)
as
select DISTINCT 币种,类别,
(select sum(f.发生额) from 表 f where datediff(day,@date,t.日期)=0 and f.类别 = t.类别) 比上日,
(select sum(w.发生额) from 表 w where datediff(month,@date,t.日期)=0 and @date>=t.date1 and w.类别 = tem.类别) 比上月,
(select sum(q.发生额) from 表 q where datediff(year,@date,t.日期)=0 and @date>=tem.date1 and q.类别 = t.类别) 比上年
from 表 ttest '2003-4-2'
测试了你们的语句,还是不行,大体是返回的数字是0,不过修改以后,效果基本满意,但查询结果仅返回本日有发生的记录的那些币种和类别,代码如下:(以查询日期为‘2003-04-02’为例)Select distinct a.日期,a.币种,a.类别,
(Select sum(发生额) from 表 where 日期=‘2003-04-02’and 类别=a.类别 and 币种=a.币种 and 科目号<'165000') as 比上日,
(Select sum(发生额) from 表 where 日期 between ‘2003-04-01’and ‘2003-04-02’ and 类别=a.类别 and 币种=a.币种 and 科目号<'165000') as 比上月,
(Select sum(发生额) from 表 where 日期 between '2003-01-01' and '2003-04-02' and 类别=a.类别 and 币种=a.币种 and 科目号<'165000') as 比上年
From 表 a
where a.日期='2003-04-02' and 科目号<'165000'
order by a.日期,a.币种,a.类别
求各位完善!