请让我知道此查询的优化.. [英] Please let me know optimization for this query..

问题描述

UPDATE #tempSDAT SET [Quad Alignment] = CASE

          WHEN [Solution For Proposal] = 'Azure' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'CRM' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Developer Tools' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Dynamics AX' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Dynamics CRM' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'EAP' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'Oracle Migrations' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'SQL' THEN 'AppPlat'
          WHEN [Solution For Proposal] = 'Desktop' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Exchange' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Lync' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Office 2010' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Office 365' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Project' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Project and Visio' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'SharePoint' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Visio' THEN 'BPIO'
          WHEN [Solution For Proposal] = 'Core Infrastructure' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Datacenter' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'ECI' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'MDOP' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Private Cloud' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'System Center' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Virtualization' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Windows 7' THEN 'Core IO'
          WHEN [Solution For Proposal] = 'Core CAL' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'EAP-ECI' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'ECAL' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'Other' THEN 'Licensing/Special Projects'
          WHEN [Solution For Proposal] = 'Windows Phone' THEN 'Licensing/Special Projects'

          ELSE [Quad Alignment]
          END

推荐答案

您可以编写查询喜欢这个

You can write your query like this

UPDATE #tempSDAT
SET [Quad Alignment]
      = CASE
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#Azure#CRM#Developer Tools#Dynamics AX#Dynamics CRM#Oracle Migrations#SQL#')  > 0  THEN 'AppPlat'
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#Desktop#Exchange#Lync#Office 2010#Office 365#Project#Project and Visio#SharePoint#Visio#')  > 0  THEN 'BPIO'
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#EAP#Core CAL#EAP-ECI#ECAL#Other#Windows Phone#')  > 0  THEN 'Licensing/Special Projects'
          WHEN CHARINDEX('#' + [Solution For Proposal] + '#','#Core Infrastructure#Datacenter#ECI#MDOP#Private Cloud#System Center#Virtualization#Windows 7#')  > 0  THEN 'Core IO'
          ELSE [Quad Alignment]
       END

另一种方法是定义，比方说：'翻译表'或'字典表'。

Another way is to define, let's say: 'translate table' or 'dictionary table'.

DECLARE @transtable TABLE (OrygValue VARCHAR(30), NewValue VARCHAR(30))
INSERT INTO @transtable (OrygValue, NewValue)
VALUES('Azure', 'AppPlat'),
('CRM', 'AppPlat'),
('Developer Tools', 'AppPlat'),

和等等...



比你需要加入两个表：

and so on...

Than you need to Join both tables:

UPDATE dstTbl SET [Quad Alignment] = srcTbl.NewValue
FROM #tempSDAT AS dstTbl INNER JOIN @transtable AS srcTbl ON dstTbl.[Quad Alignment] = @transtable.OrygValue

全部：）

That's all :)

这篇关于请让我知道此查询的优化..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！