主页 / dba / 问题 / 261458
Accepted
WXJ96163
Asked: 2020-03-09 00:27:02 +0800 CST

是否可以定义一个函数来防止在事务 sql 中重复大量“case when”？

  • 772

元帖子给出了这个代码

Select top 500
(case 
    when lower(title) like '%open source%' then 0
    when lower(title) like '%recommend%'   then 1    
    when lower(title) like '%free%'        then 2
    when lower(title) like '%good%'        then 2
    when lower(title) like '%best%'        then 2
    when lower(title) like '%better%'      then 2
    when lower(title) like '%any%'         then 2
    when lower(title) like '%what%should%' then 3
    when lower(title) like '%which%should%'then 3
    when lower(title) like '%is there%'    then 3
    when lower(title) like '%looking for%' then 4
    else 5 end
), 
left(title,25) --as [Post Link]

,ROW_NUMBER() OVER (

  PARTITION BY(case 
      when lower(title) like '%open source%' then 0
      when lower(title) like '%recommend%'   then 1    
      when lower(title) like '%free%'        then 2
      when lower(title) like '%good%'        then 2
      when lower(title) like '%best%'        then 2
      when lower(title) like '%better%'      then 2
      when lower(title) like '%any%'         then 2
      when lower(title) like '%what%should%' then 3
      when lower(title) like '%which%should%'then 3
      when lower(title) like '%is there%'    then 3
      when lower(title) like '%looking for%' then 4
      else 5 end
  )
  ORDER BY (case 
      when lower(title) like '%open source%' then 0
      when lower(title) like '%recommend%'   then 1    
      when lower(title) like '%free%'        then 2
      when lower(title) like '%good%'        then 2
      when lower(title) like '%best%'        then 2
      when lower(title) like '%better%'      then 2
      when lower(title) like '%any%'         then 2
      when lower(title) like '%what%should%' then 3
      when lower(title) like '%which%should%'then 3
      when lower(title) like '%is there%'    then 3
      when lower(title) like '%looking for%' then 4
      else 5 end
)) AS Number

from posts

where (title like '%library%'
   or title like '%tool%'
   or title like '%book%'
   or title like '%tutorial%')
and closeddate is null
and deletiondate is null

在 Python、Java 和许多其他语言中，我可以定义一个函数来防止重复大量case when. Transact sql中有类似的语法吗？

sql-server

1 个回答

  1. Best Answer

    使用应用，就像我在中显示的示例：http: //blogs.lobsterpot.com.au/2011/12/13/at-sql-tip-working-calculations/

    ...然后您可以在其他任何地方使用它作为常规列 - 只是一个计算的列。

    Select n.Num, left(title,25) --, etc
From posts 
Cross apply (select case 
  when lower(title) like '%open source%' then 0
  when lower(title) like '%recommend%'   then 1    
  when lower(title) like '%free%'        then 2
  when lower(title) like '%good%'        then 2
  when lower(title) like '%best%'        then 2
  when lower(title) like '%better%'      then 2
  when lower(title) like '%any%'         then 2
  when lower(title) like '%what%should%' then 3
  when lower(title) like '%which%should%'then 3
  when lower(title) like '%is there%'    then 3
  when lower(title) like '%looking for%' then 4
  else 5 end as Num) n
-- where ...
Order by n.num
    • 3

相关问题