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)