优先选择SQL [英] SQL Select with Priority

问题描述

我需要为给定的FriendId选择前1个最有效的折扣. 我有以下表格:

I need to select top 1 most valid discount for a given FriendId. I have the following tables:

DiscountTable -描述不同的折扣类型

DiscountTable - describes different discount types

DiscountId, Percent, Type,        Rank
1         , 20     , Friend,      2
2         , 10     , Overwrite,   1

然后我还有另外两个表(都列出了FriendIds)

Then I have another two tables (both list FriendIds)

朋友

101
102
103

覆盖

101
105

我必须为给定的FriendId选择前1个最有效的折扣.因此，对于以上数据，这将是示例输出

I have to select top 1 most valid discount for a given FriendId. So for the above data this would be sample output

Id = 101 => gets "Overwrite" discount (higher rank)
Id = 102 => gets "Friend" discount (only in friends table)
Id = 103 => gets "Friend" discount (only in friends table)
Id = 105 => gets "Overwrite" discount
Id = 106 => gets NO discount as it does not exist in neither Friend and overwrite tables


INPUT => SINGLE friendId (int). 
OUTPUT => Single DISCOUNT Record (DiscountId, Percent, Type)

覆盖和Friend表是相同的.他们只保存ID列表(单列)

Overwrites and Friend tables are the same. They only hold list of Ids (single column)

推荐答案

拥有多个结构相同的表通常是不好的做法，具有ID和Type的单个表就足够了，然后可以在JOIN到您的DiscountTable:

Having multiple tables of identical structure is usually bad practice, a single table with ID and Type would suffice, you could then use it in a JOIN to your DiscountTable:

;WITH cte AS (SELECT ID,[Type] = 'Friend' 
              FROM  Friends
              UNION  ALL
              SELECT ID,[Type] = 'Overwrite' 
              FROM  Overwrites
              )
SELECT TOP 1 a.[Type]
FROM cte a
JOIN DiscountTable DT
  ON a.[Type] = DT.[Type]
WHERE ID = '105'
ORDER BY [Rank]

请注意，不存在的ID值将不会返回.

Note, non-existent ID values will not return.

这篇关于优先选择SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！