我正在尝试根据最大值或最小值来聚合行,其中max / min由业务规则而不是固有值确定 . 换句话说,我需要创建自定义最大/最小 - 不使用CLR . 我在下面的代码段中有一个工作自包含的示例,但我觉得必须有一个更简单的方法 .
在这种特殊情况下,我需要IPA列的业务优先级由下面[IPAMap] CTE中显示的[Seq]值定义 . 然后,我从[TestData]表/ cte(此示例中只有四行)中获取一组任意行,并根据优先级将它们聚合为两行,这两行具有选定的正确IPA值 .
--Table [IPAMap] holds all possible values for [IPA], and reflects the relative
--'priority' of each with the [Seq] column. This table is used as a lookup for
--business rules.
;WITH IPAMap (Seq, IPA) AS (
SELECT 1, 'Q' UNION ALL
SELECT 2, 'S' UNION ALL
SELECT 3, 'A' UNION ALL
SELECT 4, 'L'
)
--Table [TestData] represents the real data.
, TestData (CustomerID, IPA) AS (
SELECT '123', 'A' UNION ALL
SELECT '123', 'S' UNION ALL
SELECT '234', 'L' UNION ALL
SELECT '234', 'Q'
)
--Table [PartyIPASeq] finds the lowest Seq rank from [TestData]
,PartyIPASeq (CustomerID, IPASeq) AS (
SELECT
CustomerID,
MIN(IP.Seq)
FROM TestData [TD]
INNER JOIN IPAMap [IP] ON IP.IPA = TD.IPA
GROUP BY CustomerID
)
--The final selection provides the key and the 'IPA' with the lowest rank
SELECT TD.CustomerID, TD.IPA
FROM TestData [TD]
INNER JOIN IPAMap [IP] ON IP.IPA = TD.IPA
INNER JOIN PartyIPASeq [SEQ] ON SEQ.CustomerID = [TD].CustomerID
AND SEQ.IPASeq = IP.Seq
上述查询的结果如下:
CustomerID IPA
234 S
123 Q
这是正确的,但我希望有人能告诉我一个更简单的方法来完成同样的工作 .
1 回答
看起来答案是使用带有charindex顺序的窗口函数,如下所示:
如果业务数据使用多字符值,则CHARINDEX将替换为PATINDEX函数 .