根据优先级选择记录所需的 SQL

Question

我想要一个查询，该查询将从以下数据中选择记录：

条件：仅选择组中优先级最高的记录（COB，CUR1，CUR2）。图例：1 表示最高优先级，3 表示最低优先级。

KEY     COB         CUR1    CUR2    RATE        PRIORITY
------  --------    -----   ----    --------    --------
46051   3/9/2012    AUD     USD     1.0573      1
44392   3/9/2012    AUD     USD     1.06195     2
43122   3/9/2012    AUD     USD     1.06585     3
44394   3/9/2012    BMD     USD     1.0000      2       
44395   3/9/2012    BSD     USD     1.0000      2       
46052   3/9/2012    BWP     USD     0.13875     1
44402   3/9/2012    BWP     USD     0.13925     2
46053   3/9/2012    DKK     RON     0.5859      1
44406   3/9/2012    EUR     CSD     110.62      2
46090   3/9/2012    EUR     USD     1.3109      1
44434   3/9/2012    EUR     USD     1.31185     2
43154   3/9/2012    EUR     USD     1.32556     3
44436   3/9/2012    FJD     USD     0.564       2
43156   3/9/2012    FJD     USD     0.5686      3
46092   3/9/2012    FKP     USD     1.51195     1
44437   3/9/2012    GBN     USD     1.6339      2
43185   3/9/2012    GBP     USD     1.57985     3

输出应为：

KEY     COB         CUR1    CUR2    RATE        PRIORITY
------  --------    -----   ----    --------    --------
46051   3/9/2012    AUD     USD     1.0573      1
44394   3/9/2012    BMD     USD     1.0000      2       
44395   3/9/2012    BSD     USD     1.000       2       
44402   3/9/2012    BWP     USD     0.13925     2
46053   3/9/2012    DKK     RON     0.5859      1
44406   3/9/2012    EUR     CSD     110.62      2
46090   3/9/2012    EUR     USD     1.3109      1
44436   3/9/2012    FJD     USD     0.564       2
46092   3/9/2012    FKP     USD     1.51195     1
44437   3/9/2012    GBN     USD     1.6339      2
43185   3/9/2012    GBP     USD     1.57985     3

请提供您的意见 ？

Answer 1

像这样：

WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY 
                          COB,CUR1,CUR2 ORDER BY PRIORITY) AS RowNbr,
        t.*
    FROM
        Table1 AS t
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.RowNbr=1;

Answer 2

查询太常见且太简单：

select * 
from table 
where (COB, CUR1, CUR2, PRIORITY) in 
    (select COB, CUR1, CUR2, min(PRIORITY)
     from table
     group by COB, CUR1, CUR2)

Answer 3

你可以用分析函数来做到这一点

select max(key) keep (dense_rank first order by PRIORITY) as key,
       cob,cur1,cur2,
       max(RATE) keep (dense_rank first order by PRIORITY) as RATE,
       max(PRIORITY) keep (dense_rank first order by PRIORITY) as PRIORITY,
from your_table
group by cob,cur1,cur2

Answer 4

你可以

试试这个

SELECT DISTINCT *
  FROM SO_BUFFER_TABLE_4 BT4
 WHERE BT4.KEY = (SELECT DISTINCT BT4_1.KEY
                    FROM SO_BUFFER_TABLE_4 BT4_1
                   WHERE BT4_1.COB = BT4.COB
                     AND BT4_1.CUR1 = BT4.CUR1
                     AND BT4_1.CUR2 = BT4.CUR2
                     AND BT4_1.PRIORITY =
                         (SELECT DISTINCT MIN(BT4_2.PRIORITY)
                            FROM SO_BUFFER_TABLE_4 BT4_2
                           WHERE BT4_2.COB = BT4.COB
                             AND BT4_2.CUR1 = BT4.CUR1
                             AND BT4_2.CUR2 = BT4.CUR2))
 ORDER BY BT4.CUR1

希望对你有帮助