在不同条件下从同一列中选择

Question

我要加入这两张桌子。我需要选择以下情况：

ex_head of_family_active = 1 AND tax_year = 2017

还包括：

ex_head of_family_active = 0 AND tax_year = 2016

当我第一次尝试加入这两个表时，我在from子句中得到的仓库数据dbo.tb_master_ascend AND warehouse_data.dbo.tb_master_ascend具有相同的公开名称。正如下面的查询所示，我在"where“上出现了语法错误。我做错了什么？谢谢

use [warehouse_data]

select
    parcel_number as Account,
    pact_code as type,
    owner_name as Owner,
    case 
       when ex_head_of_family_active >= 1 
          then 'X'
          else ''
    end 'Head_Of_Fam'
from 
    warehouse_data.dbo.tb_master_ascend
inner join 
    warehouse_data.dbo.tb_master_ascend on parcel_number = parcel_number
where 
    warehouse_data.dbo.tb_master_ascend.tax_year = '2016'
    and ex_head_of_family_active = 0   
where 
    warehouse_data.dbo.tb_master_ascend.t2.tax_year = '2017'
    and ex_head_of_family_active >= 1
    and (eff_from_date <= getdate())
    and (eff_to_date is null or eff_to_date >= getdate())

@marc_s我更改了where语句并更新了我的代码，但是过滤器现在不能工作了：

use [warehouse_data]

    select 
    wh2.parcel_number as Account
    ,wh2.pact_code as Class_Type
    ,wh2.owner_name as Owner_Name 

    ,case when wh2.ex_head_of_family_active >= 1 then 'X'
         else ''
    end  'Head_Of_Fam_2017'

    from warehouse_data.dbo.tb_master_ascend as WH2
          left join warehouse_data.dbo.tb_master_ascend as WH1 on ((WH2.parcel_number = wh1.parcel_number)
          and (WH1.tax_year = '2016')
          and (WH1.ex_head_of_family_active is null))
    where WH2.tax_year = '2017'
            and wh2.ex_head_of_family_active >= 1       
            and (wh2.eff_from_date <= getdate())
            and (wh2.eff_to_date is null or wh2.eff_to_date >= getdate())

Answer 1

我会用CTE来得到所有符合你2016年规定的包裹。

那就加入你2017年的包裹识别规则吧。

我总结一下：

with cte as
(
select parcelID
from
where [2016 rules]
group by parcelID --If this isn't unique you will cartisian your results
)

select columns
from table
join cte on table.parcelid=cte.parcelID
where [2017 rules]