--模拟数据--员工计件明细表IF OBJECT_ID(‘tempdb..#emp‘)>0DROP TABLE #empSELECT * INTO #empFROM (SELECT ‘586‘ empcode ,‘2014-10-21‘ opdate , 0.1 gaoduUNION ALLSELECT ‘586‘ empcode ,‘2014-10-22‘ opdate , 2.9 gaoduUNION ALLSELECT ‘586‘ empcode ,‘2014-10-24‘ opdate , 15 gaoduUNION ALLSELECT ‘admin‘ empcode ,‘2014-10-21‘ opdate , 12 gaoduUNION ALLSELECT ‘admin‘ empcode ,‘2014-10-23‘ opdate , 3 gaoduUNION ALLSELECT ‘admin‘ empcode ,‘2014-10-25‘ opdate , 7 gaodu)LSELECT * FROM #emp--计件标准表IF OBJECT_ID(‘tempdb..#bz‘)>0 DROP TABLE #bzSELECT * INTO #bzFROM (SELECT ‘gaodu<=0.1‘ guige ,‘5‘ gongshi UNION ALLSELECT ‘gaodu>0.1 and gaodu<3‘ guige ,‘4‘ gongshi UNION ALLSELECT ‘gaodu=3‘ guige ,‘3.7‘ gongshi UNION ALLSELECT ‘gaodu>3 and gaodu<18‘ guige ,‘3‘ gongshi UNION ALLSELECT ‘gaodu>=18‘ guige ,‘3.5‘ gongshi )LSELECT * FROM #bzDECLARE @sql VARCHAR(MAX),@bz VARCHAR(MAX)DECLARE @i INT ,@n INT--循环标准表IF OBJECT_ID(‘tempdb..#temp‘)>0 DROP TABLE #tempSELECT ROW_NUMBER()OVER(ORDER BY guige,gongshi) rid,*INTO #tempFROM #bzSELECT @i=MIN(rid),@n=MAX(rid) FROM #tempSET @bz=‘‘WHILE(@i<=@n)BEGIN    SELECT @bz=@bz+‘ when ‘+guige+‘ then ‘+gongshi+CHAR(10)    FROM #temp    WHERE rid=@i    SET @i=@i+1ENDPRINT @bzSET @sql=‘select empcode,opdate,gaodu,‘+CHAR(10)+‘case‘+@bz+‘ end as gongshifrom #emp ‘PRINT @sqlEXEC(@sql)

select empcode,opdate,gaodu,case when gaodu<=0.1 then 5 when gaodu=3 then 3.7 when gaodu>=18 then 3.5 when gaodu>0.1 and gaodu<3 then 4 when gaodu>3 and gaodu<18 then 3 end as gongshifrom #emp