c1 c2 c3
-----------------------------------------
产品名称 日期 数量
a 2005-05-01 21
a 2005-05-31 15
a 2005-06-01 18
a 2005-06-30 15
a 2005-07-01 19
a 2005-07-31 26
a 2005-08-31 19
b 2005-05-01 21
b 2005-05-31 15
b 2005-06-01 18
b 2005-06-30 15
b 2005-07-01 19
b 2005-07-31 26
b 2005-08-31 19
-----------------------------------------
产品名称 日期 数量
a 2005-05-01 21
a 2005-05-31 15
a 2005-06-01 18
a 2005-06-30 15
a 2005-07-01 19
a 2005-07-31 26
a 2005-08-31 19
b 2005-05-01 21
b 2005-05-31 15
b 2005-06-01 18
b 2005-06-30 15
b 2005-07-01 19
b 2005-07-31 26
b 2005-08-31 19
解决方案 »
- 存储过程报表问题求解
- 如何提高类似这样的关键词检索效率
- left outer join问题
- 为何在存储过程中的这句话不能用?select * from @DatabaseName..syscolumns
- sql server 2005中收缩数据库与收缩文件有什么区别
- 求一按天分类查询语句
- 不同網域數據庫連接問題
- 急急急:[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData (CheckforData()).
- 一个表里设置一个标识NO(INT),加入记录时NO自动增加1
- 持重分以急待
- Re:求触发器脚本(与当前时间比较相差7天,删除记录)
- 请高手看看怎么回事????? 在线急等!!!
(
c1 varchar(10),
c2 datetime,
c3 int
)insert into a values('a','2005-05-01',21)
insert into a values('a','2005-05-31',15)
insert into a values('a','2005-06-01',18)
insert into a values('a','2005-06-30',15)
insert into a values('a','2005-07-01',19)
insert into a values('a','2005-07-31',26)
insert into a values('b','2005-05-01',19)
insert into a values('b','2005-05-31',25)
insert into a values('b','2005-06-01',54)
insert into a values('b','2005-06-30',32)
insert into a values('b','2005-07-01',25)
insert into a values('b','2005-07-31',65)select c1,min(convert(char(10),c2,120)) c2,sum(c3) c3 into #t
from a
group by c1,convert(char(7),c2,120)select t1.*,t2.*, t1.c1,t1.c2,
cast((t1.c3-t2.c3) as decimal(10,2))/t2.c3 as 比上月增长率
from #t t1 left join #t t2
on cast(t1.c2 as datetime)=dateadd(month,1,cast(t2.c2 as datetime))
(c1 varchar(20),c2 datetime,c3 int)数据如上家select a.c1,m as 月份,cs as 总量
from
(select c1,datepart(month,c2) as m,sum(c3) as cs
from s as a group by c1,datepart(month,c2)) a
inner join s b on a.m=datepart(month,b.c2) and datepart(day,b.c2)=1 and a.c1=b.c1c1 月份 总量
-------------------- ----------- -----------
a 5 36
a 6 33
a 7 45
a 8 19
b 5 36
b 6 33
b 7 45
b 8 19你才说,哎!要下班了!只有做到这里了!给你一些参考,应该百分比很好求的!
my_id name1 date1 exp
----------- -------------------- --------------------------- --------------------
2 CHEN 2002-01-09 00:00:00.000 top
5 ZHANG 2002-09-08 00:00:00.000 3
6 WEI 2002-08-01 00:00:00.000 1
8 CHEN2 2003-04-02 00:00:00.000 2declare @a table(
MY_ID int,
NAME1 varchar(10),
DATE1 varchar(10),
[EXP] varchar(10)
)insert into @a values(2,'chen','2002-01-09','0')
insert into @a values(5,'ZHANG','2002-09-08','0')
insert into @a values(6,'WEI','2002-08-01','0')
insert into @a values(8,'CHEN2','2003-04-02','0')
--做了点变化
update a
set [EXP]=my_id - (select max(b.my_id) from @a b where b.my_id<a.my_id group by a.my_id)
from @a a
select * from @a
select c1,datepart(month,c2) as m,sum(c3) as cs
from s as a group by c1,datepart(month,c2)
select b.zk, b.times,sum(b.sl) as sl,sum(c.sl) as lssl from
(select zk,sl ,substring(convert(varchar(6),times,112),1,6)as times from t12) b left join
(select zk,sl ,substring(convert(varchar(6),times,112),1,6)+1as times from t12) c on b.zk=c.zk and b.times=c.times
group by b.times,b.zk
其中 zk代表商品名称,times时间,sl是数量,我已经求出了本月和上月的 数量 ,你 再加个计算列就 好 了
SQL 换为
select b.zk,b.times,b.sl as sl,c.sl as lssl from (select zk,sum(sl)as sl ,substring(convert(varchar(6),times,112),1,6)as times from t12 group by zk, substring(convert(varchar(6),times,112),1,6))b left join
(select zk,sum(sl)as sl ,substring(convert(varchar(6),times,112),1,6)+1 as times from t12 group by zk, substring(convert(varchar(6),times,112),1,6)+1) c on b.zk=c.zk and b.times=c.times
order by b.zk
(
c1 varchar(20),
c2 datetime,
c3 int
)insert into s values('a','2005-05-01',21)
insert into s values('a','2005-05-31',15)
insert into s values('a','2005-06-01',18)
insert into s values('a','2005-06-30',15)
insert into s values('a','2005-07-01',19)
insert into s values('a','2005-07-31',26)
insert into s values('b','2005-05-01',19)
insert into s values('b','2005-05-31',25)
insert into s values('b','2005-06-01',54)
insert into s values('b','2005-06-30',32)
insert into s values('b','2005-07-01',25)
insert into s values('b','2005-07-31',65)
/*--主程序--*/
select c1,cast(datepart(year,c2) as varchar)+'-'+cast(datepart(month,c2) as varchar) as 日期,sum(c3) as 月总量
into #t
from s as a group by c1,cast(datepart(year,c2) as varchar)+'-'+cast(datepart(month,c2) as varchar)
order by c1 asc,日期 ascalter table #t
add 增长百分比 decimal(5,2) default(0)
go
update #t set 增长百分比=m.增长百分比
from
(select a.c1,b.日期,cast(cast(b.月总量 as decimal(5,2))/cast(a.月总量 as decimal(5,2)) as decimal(5,2)) as 增长百分比
from #t a,#t b where a.c1=b.c1 and datediff(m,cast(a.日期+'-'+'01' as datetime),cast(b.日期+'-'+'01' as datetime))=1)
m,#t n
where m.c1=n.c1 and n.日期=m.日期select * from #tdrop table #t
/*---结果---*/
c1 日期 月总量 增长百分比
------ ----------- ------- -------------
a 2005-5 36 NULL
a 2005-6 33 .92
a 2005-7 45 1.36
b 2005-5 44 NULL
b 2005-6 86 1.95
b 2005-7 90 1.05
这样的句子,我觉得用duoluohuifeng(堕落回风)兄 的思路,用left(cast(c2 as varchar),7)要简便得多吧!
空值.