sql-server - 具有多个条件的 SQL Server 2014 查询

Question

我有一个带有多个条件的 SQL 查询，对于给定的 Source（如下所列），我需要将结果集设置为 Final（如下所列），并满足以下条件：

• 条件01：对于活动站点，应以'RO'为联系角色联系
• 条件02：对于非活动站点，应以'Owner'为联系角色联系
• 条件03：如果活动站点没有以'RO'作为联系人角色的联系人，则应以'Owner'作为联系人角色进行联系人
• 条件04：如果非活动站点没有以'Owner'为联系人角色，则应以'RO'为联系人角色联系

• 条件 05：如果 active site/inactive 没有联系 'RO' 或 'Owner' 作为联系角色，则应采取联系 'Operator' 作为联系角色

• 条件 06：仅适用于活动站点，避免使用 'RO' 作为联系人角色的 'XYZ' 联系人，并选择具有 'RO' 联系人角色的另一个联系人，如果该站点没有 'RO' 则选择 'Owner' 或 'operator'作为联系角色

• 条件 07：如果站点（活动/非活动）没有任何联系人，则这些站点应在联系人字段中具有“NULL”值。

由于原始数据集记录超过 20K，我们能否将上述所有条件包含在一个查询中（如果可能，不包括子查询）？

Source 
======

Site_Status Site_id  Site_Contact  Contact Role 
Active      123      Lilly         Owner
Active      123      Elan          RO
Inactive    345      Rose          Owner
Inactive    345      Jack          RO
Active      678      Robert        Owner 
Inactive    912      Linda         RO
Active      234      Nike          Operator 
Inactive    456      Frank         Operator
Active      808      XYZ           RO
Active      808      Kelly         Owner
Active      999      XYZ           RO
Active      999      Debbi         Operator 
Active      122                
Inactive    188              


Final
======
Site_Status Site_id Site_Contact    ContactRole
Active      123     Elan            RO
Inactive    345     Rose            Owner
Active      678     Robert          Owner
Inactive    912     Linda           RO
Active      234     Nick            Operator
Inactive    456     Frank           Operator
Active      808     Kelly           Owner
Active      999     Debbi           Operator 
Active      122     NULL            NULL
Inactive    188     NULL            NULL

提前致谢！

标签： sql-serversql-server-2014

Answer 1

像这样的东西......使用窗口函数和条件排序。

declare @YourTable table (Site_Status varchar(64), Site_id int, Site_Contact varchar(64), ContactRole varchar(64))
insert into @YourTable
values
('Active',123,'Lilly','Owner'),
('Active',123,'Elan','RO'),
('Inactive',345,'Rose','Owner'),
('Inactive',345,'Jack','RO'),
('Active',678,'Robert','Owner'),
('Inactive',912,'Linda','RO'),
('Active',234,'Nick','Operator'),
('Inactive',456,'Frank','Operator')

select
    t.*
from @YourTable t
inner join
    (select 
        Site_id
        ,Site_Status
        ,ContactRole
        ,Active = row_number() over (partition by Site_id, Site_Status order by case 
                                                                                    when Site_Status = 'Active' and ContactRole = 'RO' then 1
                                                                                    when Site_Status = 'Active' and ContactRole = 'Owner' then 2
                                                                                    when Site_Status = 'Active' and ContactRole = 'Operator' then 3
                                                                                end)
        ,InActive = row_number() over (partition by Site_id, Site_Status order by case
                                                                                    when Site_Status = 'InActive' and ContactRole = 'Owner' then 1
                                                                                    when Site_Status = 'InActive' and ContactRole = 'RO' then 2
                                                                                    when Site_Status = 'InActive' and ContactRole = 'Operator' then 3
                                                                                end)
    from @YourTable) x on 
    x.Site_id = t.Site_id 
    and x.Site_Status =  t.Site_Status 
    and t.ContactRole = x.ContactRole 
    and Active = 1 
    and InActive = 1