update 另一表 set xl=bb.xl
from (
select * from (select id,xl,case when xl='研究生' then 3 when xl='大学' then 2 when xl='大专' then 1 end as xlNUM from 表) tempa where not exists(select 1 from (select id,xl,case when xl='研究生' then 3 when xl='大学' then 2 when xl='大专' then 1 end as xlNUM from 表)tempb where tempa.xlNUM>tempb.xlNUM)
)bb where 另一表.id=bb.id
from (
select * from (select id,xl,case when xl='研究生' then 3 when xl='大学' then 2 when xl='大专' then 1 end as xlNUM from 表) tempa where not exists(select 1 from (select id,xl,case when xl='研究生' then 3 when xl='大学' then 2 when xl='大专' then 1 end as xlNUM from 表)tempb where tempa.xlNUM>tempb.xlNUM)
)bb where 另一表.id=bb.id
compute max(select @max=case x1
when '研究生' then 3
when '大学’ then 2
when '大专' then 1
end
)
by id
select * from 表 order by charindex(xl,'研究生,大学,大专')2、对同一个id号,找出最大的xl字段内容
select * from 表 tem where xl=(select top 1 xl from 表 where id=tem.id order by charindex(xl,'研究生,大学,大专'))3、然后更新另一个表里对应的id号记录的xl字段
update 另一个表 set xl=t2.xl from (select * from 表 tem where xl=(select top 1 xl from 表 where id=tem.id order by charindex(xl,'研究生,大学,大专'))) t2 where t2.id=另一个表.id
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 't2'.无效别名??
真是太厉害了!!!
I 佩服 YOU.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 't2'.
(select top 1 xl from 表 where id=tem.id order by charindex(xl,'研究生,大学,大专'))) as t2
where t2.id=另一个表.id
sql server 7.0下,实际代码如下update a01
set a01.a0405=t2.a0405,a01.a0440=t2.a0440,a01.a0410=t2.a0410,
a01.a0425=t2.a0425,a01.a0435=t2.a0435,a01.a0430=t2.a0430
from
(select * from a04 tem where a0405=(select top 1 a0405 from a04 where a0188=tem.a0188 order by charindex(a0405,'研究生,大学,大专,中专,技校,高中,初中,小学,文盲'))) as t2
where (t2.a0188=a01.a0188 and
t2.a0430=( select min(t2.a0430) from t2 where t2.a0188=a01.a0188) )Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 't2'.