双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

sql实现提取当前月份及当前月份后面5个月的数据，并有跨年度存在

2008-04-12 08:05:50 627 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

zbasic 墨衍会员 · AI 创作全网分发关注 + 加入社区

别再手动算月份了！用Oracle/Hive的months_between函数，5分钟搞定精确日期差本文详细介绍了Oracle和Hive中的months_between函数，帮助开发者快速计算精确的日期差，避免手动处理复杂逻辑。通过实战案例展示其在员工工龄计算、动态时间筛选等业务场景中的应用，提升SQL开发效率和准确性。阅读详情

表结构说明
表中包含了某产品08-09年的出货记录，有月份和年份的字段。
需要得到的结果为显示本月份及本月份后面5个月份的数据。如果为9月份的话，将要在结果中添加2009年度
1月份和2月份的数据

table_1
order_id order_year m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12 prd_id
1 2008 1 2 3 4 5 6 7 8 9 10 11 12 prd_1
2 2009 11 22 33 44 55 66 0 0 0 0 0 0 prd_1

本月份为4月，那么需要的结果为
order_id prd_id 2008_m4 2008_m5 2008_m6 2008_m7 2008_m8 2008_m9
1 prd_id 4 5 6 7 8 9

如果本月份为9，那么需要的结果为
order_id 2008_m9 2008_m10 2008_m11 2008_m12 2008_m1 2009_m2
1 9 10 11 12 11 22

建表语句如下

create table table_1(order_id smallint,order_year smallint,m1 smallint,m2 smallint,m3 smallint,m4 smallint,m5 smallint,m6 smallint,m7 smallint,m8 smallint,m9 smallint,m10 smallint,m11 smallint,m12 smallint,prd_id char(20))

insert into table_1(order_id,order_year,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,prd_id)

 select 1,2008,1,2,3,4,5,6,7,8,9,10,11,12,'prd_1'

union all  select 2,2009,11,22,33,44,55,66,0,0,0,0,0,0,'prd_1'

...全文

毕设案例|保安公司智慧管理系统的建设与开发方案 "保安公司智慧管理系统是一款专为保安行业设计的高效信息化解决方案，其核心功能涵盖了从人力资源管理到业务运营的各个方面。系统以Java作为开发语言，结合SpringMVC框架，确保了稳定性和可扩展性，而MySQL数据库则提供了强大的数据存储和处理能力。首先，系统允许用户设置各类保安，如门卫、巡逻员等，实现精细化分类管理。其次，保安信息管理模块包括详细的人事档案、资格证书、培训记录等，便于人员追踪和考核。部门和城市设置方便公司层级管理和异地项目管理。岗位设置与时间安排精确到分钟，确保排班科学合理。阅读详情

zbasic

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

zbasic 2008-04-13

打赏
举报

呵呵，开始没详细测试，大概看了一下，觉得基本上正确就没注意了。
确实是这样的。谢谢提醒。

Limpire 2008-04-12

打赏
举报

呵呵，cxmcxm 的根本达不到你的要求。

永远返回12个月不说，你 set @month = 9 试试，2008和2009年肯定是在两行上的，不是在一行。

这样还不如直接看原始表好过，搞这么一大堆代码干什么，用肉眼去看6个月的数据，也不是太难。

zbasic 2008-04-12

打赏
举报

谢谢cxmcxm ，Limpire ，相对来说，cxmcxm的好理解些，limpire的强悍些.结贴了

cxmcxm 2008-04-12

打赏
举报



create table table_1(order_id smallint,order_year smallint,m1 smallint,m2 smallint,m3 smallint,m4 smallint,m5 smallint,m6 smallint,m7 smallint,m8 smallint,m9 smallint,m10 smallint,m11 smallint,m12 smallint,prd_id char(20))

insert into table_1(order_id,order_year,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,prd_id)

 select 1,2008,1,2,3,4,5,6,7,8,9,10,11,12,'prd_1'

union all  select 2,2009,11,22,33,44,55,66,0,0,0,0,0,0,'prd_1'



declare @year int,@month int

select @year=year(getdate()),@month=month(getdate())

--select @year=2008,@month=12

if @month>8 

	select order_id,order_year,

		m1=case when 1 between @month and @month+4 then m1 else 0 end,

		m2=case when 2 between @month and @month+4 then m2 else 0 end,

		m3=case when 3 between @month and @month+4 then m3 else 0 end,

		m4=case when 4 between @month and @month+4 then m4 else 0 end,

		m5=case when 5 between @month and @month+4 then m5 else 0 end,

		m6=case when 6 between @month and @month+4 then m6 else 0 end,

		m7=case when 7 between @month and @month+4 then m7 else 0 end,

		m8=case when 8 between @month and @month+4 then m8 else 0 end,

		m9=case when 9 between @month and @month+4 then m9 else 0 end,

		m10=case when 10 between @month and @month+4 then m10 else 0 end,

		m11=case when 11 between @month and @month+4 then m11 else 0 end,

		m12=case when 12 between @month and @month+4 then m12 else 0 end

	from table_1 where order_year=@year

	union

	select  order_id,order_year,

		m1=case when 1 between @month-12 and @month+4-12 then m1 else 0 end,

		m2=case when 2 between @month-12 and @month+4-12 then m2 else 0 end,

		m3=case when 3 between @month-12 and @month+4-12 then m3 else 0 end,

		m4=case when 4 between @month-12 and @month+4-12 then m4 else 0 end,

		m5=0,m6=0,m7=0,m8=0,m9=0,m10=0,m11=0,m12=0

	

	from table_1 where order_year=@year+1

else

	select  order_id,order_year,

		m1=case when 1 between @month and @month+4 then m1 else 0 end,

		m2=case when 2 between @month and @month+4 then m2 else 0 end,

		m3=case when 3 between @month and @month+4 then m3 else 0 end,

		m4=case when 4 between @month and @month+4 then m4 else 0 end,

		m5=case when 5 between @month and @month+4 then m5 else 0 end,

		m6=case when 6 between @month and @month+4 then m6 else 0 end,

		m7=case when 7 between @month and @month+4 then m7 else 0 end,

		m8=case when 8 between @month and @month+4 then m8 else 0 end,

		m9=case when 9 between @month and @month+4 then m9 else 0 end,

		m10=case when 10 between @month and @month+4 then m10 else 0 end,

		m11=case when 11 between @month and @month+4 then m11 else 0 end,

		m12=case when 12 between @month and @month+4 then m12 else 0 end

	from table_1 where order_year=@year

zbasic 2008-04-12

打赏
举报

很强悍的语句，需要研究一下，谢了。

Limpire 2008-04-12

打赏
举报

if object_id('table_1') is not null drop table table_1

create table table_1(order_id smallint,order_year smallint,m1 smallint,m2 smallint,m3 smallint,m4 smallint,m5 smallint,m6 smallint,m7 smallint,m8 smallint,m9 smallint,m10 smallint,m11 smallint,m12 smallint,prd_id char(20))

insert into table_1(order_id,order_year,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,prd_id)

 select 1,2008,1,2,3,4,5,6,7,8,9,10,11,12,'prd_1'

union all  select 2,2009,11,22,33,44,55,66,0,0,0,0,0,0,'prd_1'

go



declare @y smallint,@m tinyint,@sql nvarchar(4000)

select @y = 2008, @m = 9

set @sql = 'select order_id=min(order_id),prd_id=min(prd_id)'



select top 6 @sql = @sql+','+quotename(ltrim(y)+'_'+name)+'=max(case order_year when '+ltrim(y)+' then '+name+' else -1 end)' from

(

	select y=@y, m=colid-2, name from syscolumns where id = object_id('table_1') and colid between 3 and 14

	union all

	select y=@y+1,m=colid-2+12, name from syscolumns where id = object_id('table_1') and colid between 3 and 14

) t

where m >=@m



set @sql = @sql + ' from table_1 where order_year in (' + case when @y < 8 then ltrim(@y) else ltrim(@y)+','+ltrim(@y+1) end+')'

exec (@sql)



/*

order_id prd_id               2008_m9 2008_m10 2008_m11 2008_m12 2009_m1 2009_m2

-------- -------------------- ------- -------- -------- -------- ------- -------

1        prd_1                9       10       11       12       11      22

*/



if object_id('table_1') is not null drop table table_1

✦双11狂欢大促✦

sql实现提取当前月份及当前月份后面5个月的数据，并有跨年度存在

相关推荐

双11狂欢大促