我现在的用法是
*** as '当月销售',*** as '一月前销售',*** as '二月前销售' 。。一共六个月的信息我想得到的效果是
*** as '7月销售',*** as '6月销售',*** as '5月销售' 。。六个月的信息,只要6个月的不要一次显示1---12月
现在是7月: 7,6,5,4,3,2,1
下个月显示就是:8,7,6,5,4,3,2
。而且月份是随系统时间变化的;
还要考虑到 a,a-1,a-2,月份是1---12的处理;
能不能为AS 别名定义一个参数
对此参数进行+ -操作显示呀
*** as '当月销售',*** as '一月前销售',*** as '二月前销售' 。。一共六个月的信息我想得到的效果是
*** as '7月销售',*** as '6月销售',*** as '5月销售' 。。六个月的信息,只要6个月的不要一次显示1---12月
现在是7月: 7,6,5,4,3,2,1
下个月显示就是:8,7,6,5,4,3,2
。而且月份是随系统时间变化的;
还要考虑到 a,a-1,a-2,月份是1---12的处理;
能不能为AS 别名定义一个参数
对此参数进行+ -操作显示呀
解决方案 »
- [mark]update后跟多个赋值表达式的用法
- 数据库还原问题,急!!!
- 紧急求救!!!关于SQLite字符串模糊查询的问题
- sql server 2000,一次写入超过20条记录就不正常,好奇怪啊。
- access数据库转入sql2000后其中一个字段无论是否有值总是输出为空的奇怪问题
- sql server 2008中,如何查询出某数据库的索引文件的大小
- msde使用osql访问报“未与信任sql server连接相关联”急呀
- 求SQL语句
- 请教大家一个实际项目中遇到的问题,是关于两张表之间的数据比较,并将比较结果以报表形式显示
- sql根据数量拆分成多行显示
- sql server表导出到文件 网上抄的一段代码,执行出不了结果,用过wsh的朋友帮忙看看问题在哪?!
- 新装一台机器,winxp,非常干净,建立一个udl文件,在文件中连接另一台电脑中的sql server数据库,可是总提示不存在拒绝访问.
/*测试数据*/
create table t(c1 int,c2 int,c3 int,c4 int,c5 int,c6 int)
insert into t select 1,2,3,4,5,6
insert into t select 7,8,9,1,2,3
select * from t
/**/
c1 c2 c3 c4 c5 c6
1 2 3 4 5 6
7 8 9 1 2 3/* as */
declare @sql varchar(8000),@n int
set @n=month(getdate())
set @sql=''
set @sql='select c1 as the'+convert(varchar,@n)+'month,c2 as the'+convert(varchar,@n-1)+'month,c3 as the'+convert(varchar,@n-2)+'month,c4 as the'+convert(varchar,@n-3)+'month,c5 as the'+convert(varchar,@n-4)+'month,c6 as the'+convert(varchar,@n-5)+'month from t'
exec (@sql)/*The result*/
the7month the6month the5month the4month the3month the2month
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 4 5 6
7 8 9 1 2 3
set @a=7declare @x int
set @x=datepart(month,getdate())declare @sql varchar(8000)
set @sql=''
while @a>0
begin
set @sql=@sql+',*** as ['+cast(@x as varchar(10))+'月销售]'
set @a=@a-1
set @x=@x-1
if @x<=0
set @x=12
endprint @sqlexec ('select aaa'+@sql+' from ...')
create table #
( date1 datetime , qty int)insert into #
select '2006-01-01' , 20 union
select '2006-01-02' , 20 union
select '2006-02-02' , 20 union
select '2006-03-02' , 20 union
select '2006-04-02' , 20 union
select '2006-05-02' , 20 union
select '2006-06-02' , 20 union
select '2006-07-02' , 20 union
select '2006-08-02' , 20 union
select '2006-09-02' , 20 union
select '2006-10-02' , 20 union
select '2006-11-02' , 20 union
select '2006-12-02' , 20
select * from #alter proc sp_monthsales @date datetime
as
begin
declare @S varchar(8000)
set @s = ''
select @S = @s+',' + ' sum(case when month(date1) = ' + max(cast(month(date1) as varchar(20))) + ' then qty else 0 end )as '''
+ max(cast(month(date1) as varchar(20))) + '月销售'''
from #
where date1 <= @date
and datediff(month , date1 ,@date ) between 0 and 5
and datediff(year,date1 , @date ) = 0 --The same year
group by month(date1)
order by month(date1) desc select @s = 'select ' + stuff(@s,1,1,'') + ' from # '-- print @S exec(@S)
endexec sp_monthsales '2006-12-12'
改
create proc sp_monthsales @date datetime
;)