update tb
set 一月 = case when 1 between startmonth and endmonth then '1' else '' end,
二月 = case when 2 between startmonth and endmonth then '1' else '' end,
三月 = case when 3 between startmonth and endmonth then '1' else '' end,
....
set 一月 = case when 1 between startmonth and endmonth then '1' else '' end,
二月 = case when 2 between startmonth and endmonth then '1' else '' end,
三月 = case when 3 between startmonth and endmonth then '1' else '' end,
....
update table1 set 二月=1 where startmonth <=2 and endmonth >=2 ;
update table1 set 三月=1 where startmonth <=3 and endmonth >=3 ;
.....
update table1 set 十二月=1 where startmonth <=12 and endmonth >=12 ;复制粘贴而已,很快就写完了
DROP TABLE TA
Go
CREATE TABLE TA(Stu_ID INT,Stu_Name NVARCHAR(2),Content NVARCHAR(2),startmonth INT,
endmonth INT,一月 NVARCHAR(1),二月 NVARCHAR(1),三月 NVARCHAR(1),
四月 NVARCHAR(1),五月 NVARCHAR(1),六月 NVARCHAR(1),七月 NVARCHAR(1))
Go
INSERT INTO TA(Stu_ID ,Stu_Name ,[Content],startmonth ,endmonth)
select 1 ,'aa' ,'aa', 1 ,4 union all
select 2 ,'bb' ,'vv', 5 ,6 union all
select 3 ,'cc','vv', 2 , 7
GO
update ta
set 一月 = case when 1 between startmonth and endmonth then '1' else '' end,
二月 = case when 2 between startmonth and endmonth then '1' else '' end,
三月 = case when 3 between startmonth and endmonth then '1' else '' end,
四月 = case when 4 between startmonth and endmonth then '1' else '' end,
五月 = case when 5 between startmonth and endmonth then '1' else '' end,
六月 = case when 6 between startmonth and endmonth then '1' else '' end,
七月 = case when 7 between startmonth and endmonth then '1' else '' end--Start
SELECT
*
FROM
TA
--Result:
/*Stu_ID Stu_Name Content startmonth endmonth 一月 二月 三月 四月 五月 六月 七月
----------- -------- ------- ----------- ----------- ---- ---- ---- ---- ---- ---- ----
1 aa aa 1 4 1 1 1 1
2 bb vv 5 6 1 1
3 cc vv 2 7 1 1 1 1 1 1(3 行受影响)*/
--End
(
Stu_ID int,
Stu_Name varchar(50),
Content varchar(50),
startmonth int,
endmonth int,
一月 int, 二月 int, 三月 int, 四月 int, 五月 int, 六月 int,七月 int,八月 int,九月 int,十月 int ,十一月 int,十二月 int
)
insert into @a (Stu_ID,Stu_Name,Content,startmonth,endmonth)
select 1, 'aa', 'aa', 1, 4 union
select 2, 'bb', 'vv', 5, 6 union
select 3, 'cc', 'vv', 2, 7update @a
set 一月 = case when 1 between startmonth and endmonth then '1' else null end,
二月 = case when 2 between startmonth and endmonth then '1' else null end,
三月 = case when 3 between startmonth and endmonth then '1' else null end,
四月 = case when 4 between startmonth and endmonth then '1' else null end,
五月 = case when 5 between startmonth and endmonth then '1' else null end,
六月 = case when 6 between startmonth and endmonth then '1' else null end,
七月 = case when 7 between startmonth and endmonth then '1' else null end,
八月 = case when 8 between startmonth and endmonth then '1' else null end,
九月 = case when 9 between startmonth and endmonth then '1' else null end,
十月 = case when 10 between startmonth and endmonth then '1' else null end,
十一月 = case when 11 between startmonth and endmonth then '1' else null end,
十二月 = case when 12 between startmonth and endmonth then '1' else null end
select * from @a
结果:
1 aa aa 1 4 1 1 1 1 NULL NULL NULL NULL NULL NULL NULL NULL
2 bb vv 5 6 NULL NULL NULL NULL 1 1 NULL NULL NULL NULL NULL NULL
3 cc vv 2 7 NULL 1 1 1 1 1 1 NULL NULL NULL NULL NULL