sql 语句的优化

Question

SELECT insurance_no 保险编号,emp_name As 姓名,'个人缴费' = CASE WHEN datediff(month,the_workdate,'2006-11-01')>=0 and datediff(month,the_workdate,'2006-11-01')/12+1<=5 THEN '10' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=6 and datediff(month,the_workdate,'2006-11-01')/12+1<=10 THEN '20' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=10 and datediff(month,the_workdate,'2006-11-01')/12+1<=15 THEN '30' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=16 and datediff(month,the_workdate,'2006-11-01')/12+1<=20 THEN '40' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=21 and datediff(month,the_workdate,'2006-11-01')/12+1<=25 THEN '50' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=26 and datediff(month,the_workdate,'2006-11-01')/12+1<=30 THEN '60' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=31 and datediff(month,the_workdate,'2006-11-01')/12+1<=35 THEN '70' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=36 THEN '80' END,'单位缴费' = CASE WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=0 and datediff(month,the_workdate,'2006-11-01')/12+1<=5 THEN '30' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=6 and datediff(month,the_workdate,'2006-11-01')/12+1<=10 THEN '60' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=10 and datediff(month,the_workdate,'2006-11-01')/12+1<=15 THEN '90' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=16 and datediff(month,the_workdate,'2006-11-01')/12+1<=20 THEN '120' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=21 and datediff(month,the_workdate,'2006-11-01')/12+1<=25 THEN '150' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=26 and datediff(month,the_workdate,'2006-11-01')/12+1<=30 THEN '180' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=31 and datediff(month,the_workdate,'2006-11-01')/12+1<=35 THEN '210' WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=36 THEN '240' END,the_workdate 参加工作时间 from YM_Employee where dept_name='公安分处'  大家帮我看看 这个语句应该怎么优化一下，在asp。net的页面中，总是提示Timeout expired. 可能是太复杂了，占用资源太大了，无法查询出结果了这个语句要实现的功能是 根据参加工作时间计算出参加工作的年数，并在此基础上再加一年的工龄如果哪位大侠能够解决，可以为您再再开散分贴 再送100

Answer 1

'个人缴费' = CASE 
WHEN datediff(month,the_workdate,'2006-11-01')>=0 and datediff(month,the_workdate,'2006-11-01')/12+1<=5 THEN '10' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=6 and datediff(month,the_workdate,'2006-11-01')/12+1<=10 THEN '20' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=10 and datediff(month,the_workdate,'2006-11-01')/12+1<=15 THEN '30' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=16 and datediff(month,the_workdate,'2006-11-01')/12+1<=20 THEN '40' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=21 and datediff(month,the_workdate,'2006-11-01')/12+1<=25 THEN '50' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=26 and datediff(month,the_workdate,'2006-11-01')/12+1<=30 THEN '60' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=31 and datediff(month,the_workdate,'2006-11-01')/12+1<=35 THEN '70' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1>=36 THEN '80' END,
---
什麽意思

Answer 2

'个人缴费' = CASE 
WHEN datediff(month,the_workdate,'2006-11-01')>=0 and datediff(month,the_workdate,'2006-11-01')/12+1<=5 THEN '10' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 6 and 10 THEN '20' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 11 and 15 THEN '30' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 16  and 20 THEN '40' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 21 and 25 THEN '50' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 26 and 30 THEN '60' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 31 and 35 THEN '70' 
else THEN '80' END,--
改為between... and 試試

Answer 3

try：
-------------------------------------------------------------------------------------
select
    insurance_no As 保险编号,
    emp_name     As 姓名,
    '个人缴费' = CASE WHEN months>=0       and months/12+1<= 5 THEN '10' 
                      WHEN months/12+1>=6  and months/12+1<=10 THEN '20' 
                      WHEN months/12+1>=10 and months/12+1<=15 THEN '30' 
                      WHEN months/12+1>=16 and months/12+1<=20 THEN '40' 
                      WHEN months/12+1>=21 and months/12+1<=25 THEN '50' 
                      WHEN months/12+1>=26 and months/12+1<=30 THEN '60' 
                      WHEN months/12+1>=31 and months/12+1<=35 THEN '70' 
                      WHEN months/12+1>=36 THEN '80' 
                 END,
    '单位缴费' = CASE WHEN months/12+1>=0  and months/12+1<= 5 THEN '30' 
                      WHEN months/12+1>=6  and months/12+1<=10 THEN '60' 
                      WHEN months/12+1>=10 and months/12+1<=15 THEN '90' 
                      WHEN months/12+1>=16 and months/12+1<=20 THEN '120' 
                      WHEN months/12+1>=21 and months/12+1<=25 THEN '150' 
                      WHEN months/12+1>=26 and months/12+1<=30 THEN '180' 
                      WHEN months/12+1>=31 and months/12+1<=35 THEN '210' 
                      WHEN months/12+1>=36 THEN '240' 
                 END,
    the_workdate As 参加工作时间
from
    (select 
         insurance_no,
         emp_name    ,
         the_workdate,
         datediff(month,the_workdate,'2006-11-01') as months 
     from 
         YM_Employee
     where
         dept_name='公安分处') t

Answer 4

把所有的>= AND <=都改為between and 

Answer 5

--trySELECT insurance_no 保险编号,emp_name As 姓名,
'个人缴费' = CASE 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 0 and 5 THEN '10' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 6 and 10 THEN '20' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 11 and 15 THEN '30'
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 16 and 20 THEN '40' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 21 and 25 THEN '50'
 WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 26 and 30 THEN '60'
 WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 31 and 35 THEN '70' 
else  '80' END,'单位缴费' = CASE 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 0 and 5 THEN '30' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 6 and 10 THEN '60' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 11 and 15 THEN '90' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 16 and 20 THEN '120' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 21 and 25 THEN '150' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 26 and 30 THEN '180' 
WHEN datediff(month,the_workdate,'2006-11-01')/12+1 between 31 and 35 THEN '210' 
else '240' END,
the_workdate 参加工作时间 from YM_Employee where dept_name='公安分处'

Answer 6

SELECT insurance_no 保险编号,emp_name As 姓名,'个人缴费' = 
CASE WHEN WorkYears>=0 and WorkYears<=5 THEN '10' 
WHEN WorkYears>=6 and WorkYears<=10 THEN '20' 
WHEN WorkYears>=10 and WorkYears<=15 THEN '30' 
WHEN WorkYears>=16 and WorkYears<=20 THEN '40' 
WHEN WorkYears>=21 and WorkYears<=25 THEN '50' 
WHEN WorkYears>=26 and WorkYears<=30 THEN '60' 
WHEN WorkYears>=31 and WorkYears<=35 THEN '70' 
WHEN WorkYears>=36 THEN '80' END,
'单位缴费' = 
CASE WHEN WorkYears>=0 and WorkYears<=5 THEN '30' 
WHEN WorkYears>=6 and WorkYears<=10 THEN '60' 
WHEN WorkYears>=10 and WorkYears<=15 THEN '90' 
WHEN WorkYears>=16 and WorkYears<=20 THEN '120' 
WHEN WorkYears>=21 and WorkYears<=25 THEN '150' 
WHEN WorkYears>=26 and WorkYears<=30 THEN '180' 
WHEN WorkYears>=31 and WorkYears<=35 THEN '210' 
WHEN WorkYears>=36 THEN '240' END,the_workdate 参加工作时间 
from (SELECT insurance_no,emp_name,WorkYears AS WorkYears FROM YM_Employee (NOLOCK) )
where dept_name='公安分处'

Answer 7

SELECT insurance_no 保险编号,emp_name As 姓名,'个人缴费' = 
CASE WHEN WorkYears>=0 and WorkYears<=5 THEN '10' 
WHEN WorkYears>=6 and WorkYears<=10 THEN '20' 
WHEN WorkYears>=10 and WorkYears<=15 THEN '30' 
WHEN WorkYears>=16 and WorkYears<=20 THEN '40' 
WHEN WorkYears>=21 and WorkYears<=25 THEN '50' 
WHEN WorkYears>=26 and WorkYears<=30 THEN '60' 
WHEN WorkYears>=31 and WorkYears<=35 THEN '70' 
WHEN WorkYears>=36 THEN '80' END,
'单位缴费' = 
CASE WHEN WorkYears>=0 and WorkYears<=5 THEN '30' 
WHEN WorkYears>=6 and WorkYears<=10 THEN '60' 
WHEN WorkYears>=10 and WorkYears<=15 THEN '90' 
WHEN WorkYears>=16 and WorkYears<=20 THEN '120' 
WHEN WorkYears>=21 and WorkYears<=25 THEN '150' 
WHEN WorkYears>=26 and WorkYears<=30 THEN '180' 
WHEN WorkYears>=31 and WorkYears<=35 THEN '210' 
WHEN WorkYears>=36 THEN '240' END,the_workdate 参加工作时间 
from (SELECT insurance_no,emp_name,datediff(month,the_workdate,'2006-11-01')/12+1 AS WorkYears FROM YM_Employee (NOLOCK) )
where dept_name='公安分处'

Answer 8

感谢
marco08(天道酬勤) 
vivdnet() 
libin_ftsafe(子陌红尘：当libin告别ftsafe) 如需要 本人将在开散分贴 再送100 请 marco08(天道酬勤) 指示

Answer 9

非常感谢 marco08(天道酬勤)

Answer 10

不用那么复杂
首先,Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
其次,可以试试用数学表达式简化case语句.
try:SELECT insurance_no 保险编号,emp_name As 姓名,datediff(month,the_workdate,'2006-11-01') as working_age,working_age/12+1 as expre,'个人缴费' = CASE WHEN working_age=0 then '0' WHEN expre<=35 THEN ceiling(expre/5)*10 WHEN expre>35 THEN 80 END,'单位缴费' = CASE WHEN working_age=0 then '0' WHEN expre<=35 THEN ceiling(expre/5)*30 WHEN expre>35 THEN 240 END,the_workdate 参加工作时间 from YM_Employee where dept_name='公安分处'

Answer 11

换行有点问题
重来
SELECT insurance_no 保险编号,emp_name As 姓名,datediff(month,the_workdate,'2006-11-01') as working_age,working_age/12+1 as expre,'个人缴费' = CASE WHEN working_age=0 then '0' WHEN expre<=35 THEN ceiling(expre/5)*10 WHEN expre>35 THEN 80 END,'单位缴费' = CASE WHEN working_age=0 then '0' WHEN expre<=35 THEN ceiling(expre/5)*30 WHEN expre>35 THEN 240 END,the_workdate 参加工作时间 from YM_Employee where dept_name='公安分处'

Answer 12

其中使用的数学表达式是基于以下考虑
范围     个人缴费
<=5        10
<=10       20
<=15       30
<=20       40
<=25       50
<=30       60
<=35       70
>35        80
设x为工龄+1
则个人缴费为  x/15 向上取整  结果乘以10
即  ceiling(x/15)*10

Answer 13

同意 joyceanne(SQL桥) 

Answer 14

感谢 joyceanne(SQL桥)
      tl_30()