有表 t1
FID Are Class
1 >=95 A
2 >=85 And <95 B
3 >=75 And <85 C
4 >=65 And <74 D
5 <65 E
表t2
EMPID SCORE Class
001 90
002 96
003 76 现在根据t1 的Are 来 Updaet t2 的 Class EMPID SCORE Class
001 90 B
002 96 A
003 76 C
FID Are Class
1 >=95 A
2 >=85 And <95 B
3 >=75 And <85 C
4 >=65 And <74 D
5 <65 E
表t2
EMPID SCORE Class
001 90
002 96
003 76 现在根据t1 的Are 来 Updaet t2 的 Class EMPID SCORE Class
001 90 B
002 96 A
003 76 C
declare @tb table([EMPID] nvarchar(3),[SCORE] int,[Class] nvarchar(1))
Insert @tb
select N'001',90,N'' union all
select N'002',96,N'' union all
select N'003',76,N''
Select * from @tb
/*
EMPID SCORE Class
----- ----------- -----
001 90
002 96
003 76
*/
UPDATE @tb
SET [Class] = CASE WHEN [SCORE]>=95 THEN 'A'
WHEN [SCORE]>=85 THEN 'B'
WHEN [SCORE]>=75 THEN 'C'
WHEN [SCORE]>=65 THEN 'D'
ELSE 'E' ENDSELECT * FROM @TB
/*
EMPID SCORE Class
----- ----------- -----
001 90 B
002 96 A
003 76 C*/
drop table ta
go
create table ta(FID int,Are varchar(50),Class varchar(50))
insert into ta select 1,'>=95','A'
insert into ta select 2,'>=85 And <95','B'
insert into ta select 3,'>=75 And <85','C'
insert into ta select 4,'>=65 And <74','D'
insert into ta select 5,'<65','E'if object_id('tb') is not null
drop table tb
go
create table tb(EMPID varchar(50),SCORE int,Class varchar(50))
insert into tb select '001',90,''
insert into tb select '002',96,''
insert into tb select '003',76,''select t.EMPID,t.SCORE,class=(select Class from ta where Are=t.Are) from (
select *,
case
when score>=95 then '>=95'
when score>=85 And score<95 then '>=85 And <95'
when score>=75 And score<85 then '>=75 And <85'
when score>=65 And score<74 then '>=65 And <74'
else '<65' end as Are
from tb)tEMPID SCORE class
001 90 B
002 96 A
003 76 C偷个懒,没直接写a,b,c,可以对应,动态的拆比较麻烦了
SET @cmd=' '
SELECT @cmd=@cmd+'UPDATE tb SET Class='''+Class+''' WHERE Score'+REPLACE(Are,'And',' AND Score') +'; ' FROM t1
EXEC (@cmd)
GO
INSERT t1 SELECT 1, '>=95' , 'A '
UNION ALL SELECT 2, '>=85 And <95' , 'B '
UNION ALL SELECT 3, '>=75 And <85' , 'C'
UNION ALL SELECT 4, '>=65 And <74' ,'D '
UNION ALL SELECT 5, '<65' ,'E '
GO
CREATE TABLE t2(EMPID VARCHAR(10), SCORE INT, Class VARCHAR)
GO
INSERT t2 SELECT '001' , 90 ,NULL
UNION ALL SELECT '002' , 96 ,NULL
UNION ALL SELECT '003', 76 ,NULL
GO
DECLARE @s VARCHAR(8000)
SET @s='UPDATE t2 SET class=CASE '
SELECT @s=@s+' WHEN score' + REPLACE(Are,'and','and score ') + ' THEN ''' + Class + '''' FROM t1
SELECT @s=@s + ' END '
EXEC(@s)
SELECT * FROM t2
GO
DROP TABLE t1,t2
GO
create table Temp
( FID int identity(1,1),
Are nvarchar(12),
Class nvarchar(2)
)
insert into Temp select '>=95','A'
union all select '>=85 And <95','B'
union all select '>=75 And <74','C'
union all select '>=65 And <74','D'
union all select '<65','E'
create table emp
(
EMPID INT ,
SCORE INT ,
Class nvarchar(2))
insert into emp select 001,90,''
union all select 002,96,''
union all select 003,76,''update emp set emp.Class = Case when SCORE>=95 THEN 'A'
when SCORE>=85 then 'B'
when SCORE>=75 THEN 'C'
when SCORE>=65 THEN 'D'
else 'E'end
from emp
SELECT * FROM EMP