数据库中有如下数据:
ID coreid term courseinfo
1265 3347 200801 11111111
1266 3346 200801 11101011
1271 3347 200803 11101001
1272 3346 200803 11101001 现在需要讲coreid值相同的cuorseinfo设置为相同,以term为标准。即结果为: ID coreid term courseinfo
1265 3347 200801 11111111
1266 3346 200801 11101011
1271 3347 200803 11111111
1272 3346 200803 11101011
由于数据量比较大,手工修改太难,但真不知道sql如何应用循环,希望高手指教,谢谢!
ID coreid term courseinfo
1265 3347 200801 11111111
1266 3346 200801 11101011
1271 3347 200803 11101001
1272 3346 200803 11101001 现在需要讲coreid值相同的cuorseinfo设置为相同,以term为标准。即结果为: ID coreid term courseinfo
1265 3347 200801 11111111
1266 3346 200801 11101011
1271 3347 200803 11111111
1272 3346 200803 11101011
由于数据量比较大,手工修改太难,但真不知道sql如何应用循环,希望高手指教,谢谢!
1265 3347 200801 11111111
1266 3346 200801 11101011
courseinfo 有相同?
courseinfo =(select top 1 courseinfo from tab where coreid=a.coreid order by term,id)
from tab a
sql语句中是否没有循环语句的写法呢?谢谢!
courseinfo =(select courseinfo from tab where coreid=a.coreid and term='200803' )
from tab a这种写法可以吗,如果term值有多个的话?
--1.将记录塞到表TestTB中
create table TestTB(ID varchar(20),coreid varchar(20),term varchar(20),courseinfo varchar(20))insert into TestTB
select 1265, 3347, 200801, 11111111 union all
select 1266, 3346, 200801, 11101011 union all
select 1271, 3357, 200803, 11101001 union all
select 1272, 3356, 200803, 12101001 union all
select 1285, 3347, 200802, 21111111 union all
select 1286, 3356, 200802, 21101011 union all
select 1281, 3347, 200803, 21101001 union all
select 1282, 3346, 200803, 22101001 select * from TestTB 结果:
ID coreid term courseinfo
-------------------- -------------------- -------------------- --------------------
1265 3347 200801 11111111
1266 3346 200801 11101011
1271 3357 200803 11101001
1272 3356 200803 12101001
1285 3347 200802 21111111
1286 3356 200802 21101011
1281 3347 200803 21101001
1282 3346 200803 22101001
--2.将记录加上编号(按Term,ID排序)塞到临时表#Temp
--注意:必须用SQL 2005才能行,SQL 2000不支持ROW_NUMBER()函数
select ROW_NUMBER() OVER (ORDER BY term,id) AS 'RowNumber',*
into #Temp from TestTBselect * from #Temp
--3.将#Temp表中以term为标准的第一条Courseinfo找出来塞至临时表#TempToUpdate中
select *
into #TempToUpdate
from #Temp a where rowNumber
in(select top 1 rowNumber from #Temp where coreid=a.coreid order by rowNumber)select * from #TempToUpdateRowNumber ID coreid term courseinfo
------------ ---------------- ------------ -------------------- --------------------
1 1265 3347 200801 11111111
2 1266 3346 200801 11101011
4 1286 3356 200802 21101011
5 1271 3357 200803 11101001
--4.更新表TestTB
update TestTB set courseinfo=b.courseinfo from TestTB a,#TempToUpdate b
where a.coreid=b.coreidselect * from TestTBID coreid term courseinfo
-------------------- -------------------- -------------------- --------------------
1265 3347 200801 11111111
1266 3346 200801 11101011
1271 3357 200803 11101001
1272 3356 200803 21101011
1285 3347 200802 11111111
1286 3356 200802 21101011
1281 3347 200803 11111111
1282 3346 200803 11101011 (8 行受影响)
sql2005with cte as (
select id,coreid,term,courseinfo,rowid=row_number() over(partition by coreid order by term)
from #T
)
update #T set courseinfo = b.courseinfo
from #T a
inner join (select * from cte where rowid=1) b on a.coreid=b.coreid
不知道我想的对不对,继续思考ing……sql我总是似懂非懂的,看来还得继续学习,谢谢各位了。