还是SQL查询语句,能优化的的帮忙一下 ( 积分: 10 )

  • 主题发起人 主题发起人 take2001
  • 开始时间 开始时间
T

take2001

Unregistered / Unconfirmed
GUEST, unregistred user!
select&nbsp;id,name,sex,birday&nbsp;from&nbsp;employe&nbsp;<br>where<br>(sex='女'&nbsp;and&nbsp;ts&lt;&gt;'a'&nbsp;and&nbsp;ts&lt;&gt;'b'&nbsp;and&nbsp;(select&nbsp;datediff(day,birday,getdate())/365)&gt;=50)<br>or<br>(sex='女'&nbsp;and&nbsp;(ts='a'&nbsp;or&nbsp;ts='b')&nbsp;and&nbsp;(select&nbsp;datediff(day,birday,getdate())/365)&gt;=55)<br>我的题意是:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;如果是女孩子要是不在岗位a或者不在岗位b的话50岁就可以退休了,要是在岗位a或者在岗位b的话,就要到55岁才能退休!<br>以上那SQL语句是查出到达退休年龄的女员工,求优化!!肯定有别的写法的!!!
 
select&nbsp;id,name,sex,birday&nbsp;from&nbsp;employe&nbsp;<br>where<br>sex='女'&nbsp;and&nbsp;((ts&lt;&gt;'a'&nbsp;and&nbsp;ts&lt;&gt;'b'&nbsp;and&nbsp;datediff(day,birday,getdate())/365&gt;=50)<br>or&nbsp;((ts='a'&nbsp;or&nbsp;ts='b')&nbsp;and&nbsp;datediff(day,birday,getdate())/365&gt;=55))
 
楼上的这样会快些吗!<br>我的题意是:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;如果是女孩子要是不在岗位a或者不在岗位b的话50岁就可以退休了,要是在岗位a或者在岗位b的话,就要到55岁才能退休!<br>有会的,无私奉献一下吧!!嘿嘿~!
 
这个为什么不可以用????<br>DATEDIFF(Year,born,Getdate())
 
试一下这样写&nbsp;<br>select&nbsp;id,&nbsp;name,&nbsp;sex,&nbsp;birday<br>&nbsp;&nbsp;from&nbsp;employe<br>&nbsp;where&nbsp;sex&nbsp;=&nbsp;'女'<br>&nbsp;&nbsp;&nbsp;and&nbsp;ts&nbsp;&lt;&gt;&nbsp;'a'<br>&nbsp;&nbsp;&nbsp;and&nbsp;ts&nbsp;&lt;&gt;&nbsp;'b'<br>&nbsp;&nbsp;&nbsp;and&nbsp;datediff(day,&nbsp;birday,&nbsp;getdate())&nbsp;&gt;=&nbsp;50&nbsp;*&nbsp;365<br>union&nbsp;all<br>select&nbsp;id,&nbsp;name,&nbsp;sex,&nbsp;birday<br>&nbsp;&nbsp;from&nbsp;employe<br>&nbsp;where&nbsp;sex&nbsp;=&nbsp;'女'<br>&nbsp;&nbsp;&nbsp;and&nbsp;ts&nbsp;=&nbsp;'a'<br>&nbsp;&nbsp;&nbsp;and&nbsp;datediff(day,&nbsp;birday,&nbsp;getdate())&nbsp;&gt;=&nbsp;55&nbsp;*&nbsp;365<br>union&nbsp;all<br>select&nbsp;id,&nbsp;name,&nbsp;sex,&nbsp;birday<br>&nbsp;&nbsp;from&nbsp;employe<br>&nbsp;where&nbsp;sex&nbsp;=&nbsp;'女'<br>&nbsp;&nbsp;&nbsp;and&nbsp;ts&nbsp;=&nbsp;'b'&nbsp;and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;datediff(day,&nbsp;birday,&nbsp;getdate())&nbsp;&nbsp;&nbsp;&gt;=&nbsp;55*365
 
顶<br>------------------------------------------------------<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;http://www.waibaoinfo.com&nbsp;外包信息网
 
select&nbsp;id,name,sex,birday&nbsp;from&nbsp;employe<br>where&nbsp;sex='女'&nbsp;and&nbsp;datediff(day,birday,getdate())/365&gt;=<br>case&nbsp;when&nbsp;ts&nbsp;in('a','b')&nbsp;then&nbsp;55<br>else&nbsp;50<br>end
 
DATEDIFF(Year,born,Getdate())&nbsp;只精确到年份,计算年龄一般都是精确到月份的啊!
 
select&nbsp;id,name,sex,birday&nbsp;from&nbsp;employe&nbsp;<br>where<br>(sex='女'&nbsp;and&nbsp;ts&lt;&gt;'a'&nbsp;and&nbsp;ts&lt;&gt;'b'&nbsp;and&nbsp;(select&nbsp;datediff(day,birday,getdate())/365)&gt;=50)<br>or<br>(sex='女'&nbsp;and&nbsp;(ts='a'&nbsp;or&nbsp;ts='b')&nbsp;and&nbsp;(select&nbsp;datediff(day,birday,getdate())/365)&gt;=55)<br>跟楼上的那个Case&nbsp;then的哪个比较快,为什么
 
分给我呀~
 
总的也就10分!我再去申请个吧!<br>总的把问题的原因搞清楚吧!
 
SQL&nbsp;Expert比较吧
 
后退
顶部