具有不同where子句的多个sql查询

I have multiple sql queries with different where clauses:

我有多个不同where子句的sql查询:

select * from (
  SELECT  ov.state_nm, ov.state_id,us.client_id, us.rebate_qtr_id, 
  sum(cvr.rebate_per_claim) as flagged
  FROM [dOVT_OutlierView].[APP01DBA].[OVT_USER_STATE_ASSIGNMENT] us 
  inner join ovt_states ov on us.state_id=ov.state_id 
  inner join OVT_LABELER_CLIENT_STATE_SETTINGS css 
  on css.client_id=us.client_id and us.state_id=css.state_id
  inner join [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT] cvr 
  on us.client_id= cvr.client_id 
  and css.client_id=cvr.client_id
  inner join [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT_USER_DECISION] udes 
  on udes.client_id=cvr.client_id
  and udes.client_id=css.client_id and us.client_id=udes.client_id
  where us.assigned_to_user_id=81 and us.company_id=50 and us.client_id=23

  group by ov.state_id, us.client_id,ov.state_nm,us.rebate_qtr_id) a
  cross join (

  SELECT  
  sum(cvr.rebate_per_claim) as follwup
  FROM [dOVT_OutlierView].[APP01DBA].[OVT_USER_STATE_ASSIGNMENT] us 
  inner join ovt_states ov on us.state_id=ov.state_id 
  inner join OVT_LABELER_CLIENT_STATE_SETTINGS css 
  on css.client_id=us.client_id and us.state_id=css.state_id
  inner join [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT] cvr 
  on us.client_id= cvr.client_id 
  and css.client_id=cvr.client_id
  inner join [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT_USER_DECISION] udes 
  on udes.client_id=cvr.client_id
  and udes.client_id=css.client_id and us.client_id=udes.client_id
  where us.assigned_to_user_id=81 and us.company_id=50 and us.client_id=23
  and udes.followup_dispute_ignore_flg='F'
  group by ov.state_id, us.client_id,ov.state_nm,us.rebate_qtr_id) b

The above two select queries in brackets are the same, only the where clause is added in second query. For merging purposes only, I have added cross join to both the queries.

括号中的两个select查询是相同的，只有where子句被添加到第二个查询中。为了合并，我在两个查询中都添加了cross join。

Is any other way to do this without repeating the queries again ?

是否有其他方法可以在不重复查询的情况下执行此操作?

2 个解决方案

#1

I would just put this in the select part of the first query, remove the cross join part, and you should get what you need.

我将把它放在第一个查询的select部分，删除交叉连接部分，您应该得到您需要的。

sum(case when udes.followup_dispute_ignore_flg='F' then cvr.rebate_per_claim else 0 end )as follwup

所以

select
  ov.state_nm, ov.state_id,
  us.client_id, 
  us.rebate_qtr_id, 
  sum(cvr.rebate_per_claim) as flagged,
  -- just a new line here
  sum(case when udes.followup_dispute_ignore_flg='F' then cvr.rebate_per_claim else 0 end )as follwup
from [dOVT_OutlierView].[APP01DBA].[OVT_USER_STATE_ASSIGNMENT] us 
inner join ovt_states ov on us.state_id=ov.state_id 
inner join OVT_LABELER_CLIENT_STATE_SETTINGS css 
  on css.client_id=us.client_id and us.state_id=css.state_id
inner join [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT] cvr 
  on us.client_id= cvr.client_id and css.client_id=cvr.client_id
inner join [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT_USER_DECISION] udes 
  on udes.client_id=cvr.client_id and udes.client_id=css.client_id and us.client_id=udes.client_id
where us.assigned_to_user_id=81 and us.company_id=50 and us.client_id=23
group by ov.state_id, us.client_id,ov.state_nm,us.rebate_qtr_id

#2

You could do something like this. Build your main query first, with all of your joins. Then build your summary queries off of that. Then cross join the two summary queries at the end.

你可以这样做。首先构建主查询，并使用所有连接。然后构建您的总结查询。然后交叉连接最后的两个汇总查询。

;WITH MasterQuery
AS (
    SELECT ov.state_nm
        ,ov.state_id
        ,us.client_id
        ,us.rebate_qtr_id
        ,cvr.rebate_per_claim
        ,udes.followup_dispute_ignore_flg
    FROM [dOVT_OutlierView].[APP01DBA].[OVT_USER_STATE_ASSIGNMENT] us
    INNER JOIN ovt_states ov
        ON us.state_id = ov.state_id
    INNER JOIN OVT_LABELER_CLIENT_STATE_SETTINGS css
        ON css.client_id = us.client_id
            AND us.state_id = css.state_id
    INNER JOIN [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT] cvr
        ON us.client_id = cvr.client_id
            AND css.client_id = cvr.client_id
    INNER JOIN [APP01DBA].[OVT_CLAIM_VALIDATION_ALL_RESULT_USER_DECISION] udes
        ON udes.client_id = cvr.client_id
            AND udes.client_id = css.client_id
            AND us.client_id = udes.client_id
    WHERE us.assigned_to_user_id = 81
        AND us.company_id = 50
        AND us.client_id = 23
    )
    ,FirstSummaryQuery
AS (
    SELECT state_nm
        ,state_id
        ,client_id
        ,rebate_qtr_id
        ,sum(cvr.rebate_per_claim) AS flagged
    FROM MasterQuery
    GROUP BY state_nm
        ,state_id
        ,client_id
        ,rebate_qtr_id
    )
    ,SeconndSummaryQuery
AS (
    SELECT sum(rebate_per_claim) AS follwup
    FROM MasterQuery
    WHERE followup_dispute_ignore_flg = 'F'
    )
SELECT state_nm
    ,state_id
    ,client_id
    ,rebate_qtr_id
    ,follwup
FROM FirstSummaryQuery
CROSS JOIN SeconndSummaryQuery

#1