Оператор SQL Union all

У меня было оператор join 4 sql, используя UNION ALL.

поскольку условие WHERE одинаково, можно ли объединить условие WHERE в одно?

select 'Transfer In' as MovementType, * from vHRIS_StaffMovement_TransferIn 
where cur_deptid in (1,2,3,4,5)
        and cast(EffectiveDate as date) <='2013-08-02'
        and cast(EffectiveDate as date) >= '2012-08-01'
        and StaffType in (1,2,3,4,5)

union all

        select 'Terminate' as MovementTyep, * from vHRIS_StaffMovement_Terminate  
where cur_deptid in (1,2,3,4,5)
        and cast(EffectiveDate as date) <='2013-08-02'
        and cast(EffectiveDate as date) >= '2012-08-01'
        and StaffType in (1,2,3,4,5)

union all

        select 'New Hire' as MovementTyep, * from vHRIS_StaffMovement_NewHire 
where cur_deptid in (1,2,3,4,5)
        and cast(EffectiveDate as date) <='2013-08-02'
        and cast(EffectiveDate as date) >= '2012-08-01'
        and StaffType in (1,2,3,4,5)      

union all

select 'Transfer Out' as MovementType, * from vHRIS_StaffMovement_TransferOut 
where cur_deptid in (1,2,3,4,5)
        and cast(EffectiveDate as date) <='2013-08-02'
        and cast(EffectiveDate as date) >= '2012-08-01'
        and StaffType in (1,2,3,4,5)

sql where-clause union union-all
person Joe Yan    schedule 29.08.2013    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Вы можете сделать это следующим образом:

    select * from (
        select 'Transfer In' as MovementType, * from vHRIS_StaffMovement_TransferIn 
        union all
        select 'Terminate' as MovementTyep, * from vHRIS_StaffMovement_Terminate  
        union all
        select 'New Hire' as MovementTyep, * from vHRIS_StaffMovement_NewHire 
        union all
        select 'Transfer Out' as MovementType, * from vHRIS_StaffMovement_TransferOut ) as a
where cur_deptid in (1,2,3,4,5)
        and cast(EffectiveDate as date) <='2013-08-02'
        and cast(EffectiveDate as date) >= '2012-08-01'
        and StaffType in (1,2,3,4,5)
person Yonabart    schedule 29.08.2013

arrow_upward
1
arrow_downward

Вы можете использовать подзапрос:

SELECT X.* 
FROM   (SELECT 'Transfer In' AS MovementType, * 
        FROM   vhris_staffmovement_transferin 

        UNION ALL 

        SELECT 'Terminate' AS MovementTyep, * 
        FROM   vhris_staffmovement_terminate 

        UNION ALL 

        SELECT 'New Hire' AS MovementTyep,  * 
        FROM   vhris_staffmovement_newhire

        UNION ALL 

        SELECT 'Transfer Out' AS MovementType, * 
        FROM   vhris_staffmovement_transferout ) X 
WHERE  cur_deptid IN ( 1, 2, 3, 4, 5 ) 
       AND Cast(effectivedate AS DATE) <= '2013-08-02' 
       AND Cast(effectivedate AS DATE) >= '2012-08-01' 
       AND stafftype IN ( 1, 2, 3, 4, 5 )
person Tim Schmelter    schedule 29.08.2013

arrow_upward
0
arrow_downward

Вы можете попробовать это:

SELECT *
  FROM T_IP_CHARGES WITH (NOLOCK)
  WHERE PostingDate BETWEEN '01/01/2014' AND '01/31/2014'
    AND DetailTotalCharges > 0
  UNION ALL
  SELECT *
  FROM T_OP_CHARGES WITH (NOLOCK)
  WHERE PostingDate BETWEEN '01/01/2014' AND '01/31/2014'
  AND DetailTotalCharges > 0
person JosueXIX    schedule 24.04.2014