动态读取标准表高度尺寸查询

--模拟数据
--员工计件明细表
IF OBJECT_ID('tempdb..#emp')>0
DROP TABLE #emp
SELECT * INTO #emp
FROM (
SELECT '586' empcode ,'2014-10-21' opdate , 0.1 gaodu
UNION ALL
SELECT '586' empcode ,'2014-10-22' opdate , 2.9 gaodu
UNION ALL
SELECT '586' empcode ,'2014-10-24' opdate , 15 gaodu
UNION ALL
SELECT 'admin' empcode ,'2014-10-21' opdate , 12 gaodu
UNION ALL
SELECT 'admin' empcode ,'2014-10-23' opdate , 3 gaodu
UNION ALL
SELECT 'admin' empcode ,'2014-10-25' opdate , 7 gaodu)L

SELECT * FROM #emp

--计件标准表
IF OBJECT_ID('tempdb..#bz')>0 DROP TABLE #bz
SELECT * INTO #bz
FROM (
SELECT 'gaodu<=0.1' guige ,'5' gongshi 
UNION ALL
SELECT 'gaodu>0.1 and gaodu<3' guige ,'4' gongshi 
UNION ALL
SELECT 'gaodu=3' guige ,'3.7' gongshi 
UNION ALL
SELECT 'gaodu>3 and gaodu<18' guige ,'3' gongshi 
UNION ALL
SELECT 'gaodu>=18' guige ,'3.5' gongshi )L

SELECT * FROM #bz

DECLARE @sql VARCHAR(MAX),@bz VARCHAR(MAX)
DECLARE @i INT ,@n INT

--循环标准表
IF OBJECT_ID('tempdb..#temp')>0 DROP TABLE #temp
SELECT ROW_NUMBER()OVER(ORDER BY guige,gongshi) rid
,*
INTO #temp
FROM #bz

SELECT @i=MIN(rid),@n=MAX(rid) FROM #temp
SET @bz=''
WHILE(@i<=@n)
BEGIN
    SELECT @bz=@bz+' when '+guige+' then '+gongshi+CHAR(10)
    FROM #temp
    WHERE rid=@i
    SET @i=@i+1
END
PRINT @bz

SET @sql='select empcode,opdate,gaodu,'+CHAR(10)+'case'+@bz+' end as gongshi
from #emp '
PRINT @sql

EXEC(@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 gongshi
from #emp