如上图:
左表中用户编号HM_01, 2012年1至3月,月份连续,其他行数据月份不连续
想根据左边表数据 组织一下, 生成右侧表。
如果年份相同,月份连续, 则右侧表中开始月为最小月1,结束月为3月
如果月份不连续,则开始月和结束月相同,上面表编号HM-01是举例说明,实际数据比较多。 请教各位该如何编写这个sql语句
左表中用户编号HM_01, 2012年1至3月,月份连续,其他行数据月份不连续
想根据左边表数据 组织一下, 生成右侧表。
如果年份相同,月份连续, 则右侧表中开始月为最小月1,结束月为3月
如果月份不连续,则开始月和结束月相同,上面表编号HM-01是举例说明,实际数据比较多。 请教各位该如何编写这个sql语句
if not object_id('tb') is null drop table tb
create table tb (I_Y int,I_M int,I_BH char(10))
insert tb select 2012,1,'A101'
insert tb select 2012,2,'A101'
insert tb select 2012,3,'A101'
insert tb select 2012,11,'A101'
insert tb select 2013,7,'A101'
insert tb select 2011,12,'A101'
goselect * into #a from ( select i_y,i_m from tb a where i_m not in (select i_m+1 from tb where a.i_y=i_y))aaaa
alter table #a add id int identity(1,1)select * into #b from ( select i_y,i_m from tb a where i_m not in (select i_m-1 from tb where a.i_y=i_y))aaaa
alter table #b add id int identity(1,1)select aa.i_y,aa.i_m i_b,bb.i_m i_e from #a aa cross join
#b bb where aa.id=bb.id drop table #a,#b
create table tb (I_Y int,I_M int,I_BH char(10),bn CHAR(10))
insert tb select 2012,1,'A101','HM_01'
insert tb select 2012,2,'A101','HM_01'
insert tb select 2012,3,'A101','HM_01'
insert tb select 2012,11,'A101','HM_01'
insert tb select 2013,7,'A101','HM_01'
insert tb select 2011,12,'A101','HM_01'
goselect * into #a from ( select i_y,i_m ,bn from tb a where i_m not in (select i_m+1 from tb where a.i_y=i_y))aaaa
alter table #a add id int identity(1,1)select * into #b from ( select i_y,i_m ,bn from tb a where i_m not in (select i_m-1 from tb where a.i_y=i_y))aaaa
alter table #b add id int identity(1,1)select aa.i_y,aa.i_m i_b,bb.i_m i_e ,AA.bn from #a aa cross join
#b bb where aa.id=bb.id drop table #a,#b
/*
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(4 行受影响)(4 行受影响)
i_y i_b i_e bn
----------- ----------- ----------- ----------
2012 1 3 HM_01
2012 11 11 HM_01
2013 7 7 HM_01
2011 12 12 HM_01 (4 行受影响)
我那你这个语句在我真实库执行,结果不完整,应该是编号的原因 我这儿编号只有一个
实际数据库中编号很多
我发一批编号您帮我看一下
1024 2013 6
1024 2013 7
1073 2013 7
1099 2011 3
1099 2011 4
1099 2011 5
1099 2011 6
1153 2013 4
1153 2013 5
1153 2013 6
1444 2011 12
1444 2012 1
1444 2012 2
1444 2012 3
1468 2012 10
1468 2012 11
1468 2012 12
7952 2013 6
7952 2013 7
4600 2013 5
4600 2013 7
加个条件。if not object_id('tb') is null drop table tb
create table tb (I_Y int,I_M int,I_BH char(10),bn CHAR(10))
insert tb select 2012,1,'A101','HM_01'
insert tb select 2012,2,'A101','HM_01'
insert tb select 2012,3,'A101','HM_03'
insert tb select 2012,11,'A101','HM_06'
insert tb select 2013,7,'A101','HM_01'
insert tb select 2011,12,'A101','HM_09'
goselect * into #a from ( select i_y,i_m ,bn from tb a where i_m not in (select i_m+1 from tb where a.i_y=i_y and a.bn=bn))aaaa
alter table #a add id int identity(1,1)select * into #b from ( select i_y,i_m ,bn from tb a where i_m not in (select i_m-1 from tb where a.i_y=i_y and a.bn=bn))aaaa
alter table #b add id int identity(1,1)select aa.i_y,aa.i_m i_b,bb.i_m i_e ,AA.bn from #a aa cross join
#b bb where aa.id=bb.id drop table #a,#b/*(5 行受影响)(5 行受影响)
i_y i_b i_e bn
----------- ----------- ----------- ----------
2012 1 2 HM_01
2012 3 3 HM_03
2012 11 11 HM_06
2013 7 7 HM_01
2011 12 12 HM_09 (5 行受影响)*/
我那你这个语句在我真实库执行,结果不完整,应该是编号的原因 我这儿编号只有一个
实际数据库中编号很多
我发一批编号您帮我看一下
1024 2013 6
1024 2013 7
1073 2013 7
1099 2011 3
1099 2011 4
1099 2011 5
1099 2011 6
1153 2013 4
1153 2013 5
1153 2013 6
1444 2011 12
1444 2012 1
1444 2012 2
1444 2012 3
1468 2012 10
1468 2012 11
1468 2012 12
7952 2013 6
7952 2013 7
4600 2013 5
4600 2013 7
加个条件。if not object_id('tb') is null drop table tb
create table tb (I_Y int,I_M int,I_BH char(10),bn CHAR(10))
insert tb select 2012,1,'A101','HM_01'
insert tb select 2012,2,'A101','HM_01'
insert tb select 2012,3,'A101','HM_03'
insert tb select 2012,11,'A101','HM_06'
insert tb select 2013,7,'A101','HM_01'
insert tb select 2011,12,'A101','HM_09'
goselect * into #a from ( select i_y,i_m ,bn from tb a where i_m not in (select i_m+1 from tb where a.i_y=i_y and a.bn=bn))aaaa
alter table #a add id int identity(1,1)select * into #b from ( select i_y,i_m ,bn from tb a where i_m not in (select i_m-1 from tb where a.i_y=i_y and a.bn=bn))aaaa
alter table #b add id int identity(1,1)select aa.i_y,aa.i_m i_b,bb.i_m i_e ,AA.bn from #a aa cross join
#b bb where aa.id=bb.id drop table #a,#b/*(5 行受影响)(5 行受影响)
i_y i_b i_e bn
----------- ----------- ----------- ----------
2012 1 2 HM_01
2012 3 3 HM_03
2012 11 11 HM_06
2013 7 7 HM_01
2011 12 12 HM_09 (5 行受影响)*/可以使用了,非常感谢!!!