如何在sql server中确定记录的优先级？

由 发布于

I want to try to write a query. I need to take the Targetvalue from #MetricTargets table for the MetricID's which are there in #Metrics.

{
CREATE TABLE #Metrics(
MetricId BiginT,
AccountId Bigint,
CountryId Bigint
)

INSERT INTO #Metrics(MetricId,AccountId,CountryId)select 8253,3,105

Create table #MetricTargets(
AccountId BIGINT,
MetricId BIGINT,
TargetValue BIGINT,
Countryid BIGINT
)

INSERT  INTO #MetricTargets(AccountId,TargetValue,MetricId,Countryid)SELECT 105,100,3,8253
INSERT  INTO #MetricTargets(AccountId,TargetValue,MetricId,Countryid)SELECT -1,80,3,8253
INSERT  INTO #MetricTargets(AccountId,TargetValue,MetricId,Countryid)SELECT 105,99,-1,8253 
}

CountryId = -1和AccountId = -1代表所有国家和帐户

So I want to retrieve Targetvalue for a metricId if the AccountId and CountryId is given in #MetricTargets table in first Priority , AccountId =Something and countryId = -1 is 2 nd priority,ACcountId=-1 and CountryId= SomeThing then 3rd priority and AccountId =-1 and CountryId=-1 then last priority.

我写了下面的查询，但它提供了所有记录。

select M.TargetValue from #Metrics S   
LEFT JOIN #MetricsTargets M
ON  M.MetricId = S.MetricId AND (S.AccountId+M.AccountId<S.AccountId or S.AccountId = M.AccountId)
                               AND (S.CountryId+M.CountryId<S.CountryId or S.CountryId=M.CountryId)