如何在sql server中对记录进行优先级排序

Question

我想尝试编写一个查询。我需要从#MetricTargets表中获取位于#Metrics中的MetricID的Targetvalue。

{
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代表所有国家和账户

因此，如果AccountId和CountryId在#MetricTargets表中以第一优先级给定，AccountId=Something和CountryId=-1是第二优先级，AccountId=-1和CountryId=SometThing是第三优先级，AccountId=-1和CountryId是最后优先级，我想检索metricId的Targetvalue。

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

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)

Answer 1

您似乎希望将-1作为优先级较低的通配符进行匹配。我很确定横向连接可以满足您的要求：

select . . . 
from #Metrics m OUTER APPLY
(select top (1) mt.*
from #MetricsTargets mt
where mt.MetricId = m.MetricId and
m.account_id in (mt.account_id, -1) and
m.country_id in (mt.country_id, -1)
order by (case when mt.account_id = m.account_id and mt.country_id = m.country_id
then 1
when mt.account_id = m.account_id and mt.country_id = -1
then 2
when mt.account_id = -1 and mt.country_id = m.country_id
then 3
when mt.account_id = -1 and mt.country_id = -1
then 4
end)
) mt