子选择和分组:从包含组最大值的行中获取名称列

Question

我有两个表：状态和项目。

States ：

+----+------+-------+----------+
| id | name | state | priority |
+----+------+-------+----------+
|  1 |   AA |    10 |        1 |
|  2 |   AB |    10 |        2 |
|  3 |   AC |    10 |        3 |
|  4 |   BA |    20 |        1 |
|  5 |   BB |    20 |        5 |
|  6 |   BC |    20 |       10 |
|  7 |   BD |    20 |       50 |
+----+------+-------+----------+

Items ：

+----+--------+-------+
| id | item   | state |
+----+--------+-------+
|  1 |   Blue |    10 |
|  2 |    Red |    20 |
|  3 |  Green |    20 |
|  4 | Yellow |    10 |
|  5 |  Brown |    10 |
+----+--------+-------+

Items表中未使用priority列，但使获取所需数据变得复杂，如下所示。

我想要的是Items表中的行列表，将每行中的state.id值替换为优先级最高的state name。

结果将如下所示：

+----+--------+-------+
| id | item   | state |
+----+--------+-------+
|  1 |   Blue |    AC |
|  2 |    Red |    BD |
|  3 |  Green |    BD |
|  4 | Yellow |    AC |
|  5 |  Brown |    AC |
+----+--------+-------+

这是我想出的小怪物。这是最好的方法，还是我可以更有效率/不那么冗长？（子选择让我手掌发痒。

SELECT *
FROM
  Items AS itm
  INNER JOIN (SELECT sta.name, sta.state
              FROM (SELECT state, MAX(priority) [highest]
                    FROM States
                    GROUP BY state) AS pri
                INNER JOIN States AS sta
                  ON sta.state = pri.state
                     AND sta.priority = pri.highest) AS nam
    ON item.state = name.state

更新：我正在使用 MS-SQL 2005 和 MS-SQL 2008R2

Answer 1

您没有发布您的 SQL 服务器版本。假设您使用的是 2005 或更高版本，您可以将 ROW_NUMBER（） 函数与交叉应用一起使用，如下所示：

 CREATE TABLE dbo.States(id INT, name NVARCHAR(25), state INT, priority INT);
 INSERT INTO dbo.States
 VALUES
(  1 ,'AA',    10 ,        1 ),
(  2 ,'AB',    10 ,        2 ),
(  3 ,'AC',    10 ,        3 ),
(  4 ,'BA',    20 ,        1 ),
(  5 ,'BB',    20 ,        5 ),
(  6 ,'BC',    20 ,       10 ),
(  7 ,'BD',    20 ,       50 );
CREATE TABLE dbo.Items( id INT ,item NVARCHAR(25), state INT );
INSERT INTO dbo.Items
VALUES
(  1 ,'Blue',    10 ),
(  2 ,'Red',    20 ),
(  3 ,'Green',    20 ),
(  4 ,'Yellow',    10 ),
(  5 ,'Brown',    10 );

SELECT  i.id,
        i.item,
        s.name,
        s.priority
FROM dbo.Items i
CROSS APPLY (
SELECT *,ROW_NUMBER()OVER(ORDER BY priority DESC) rn FROM dbo.States si WHERE si.state = i.state 
)s
WHERE s.rn = 1;

交叉应用的工作方式类似于连接，但允许引用右侧左侧的列，如 where 子句所示。ROW_NUMBER（） 函数以相反的优先级顺序对状态表中与当前状态值匹配的所有行进行编号，以便具有最高优先级的行始终获得数字 1。最后一个 where 子句只过滤掉这些行。

编辑：

我刚刚开始了一个关于加入的博客系列：加入一天
交叉申请将是第8天（12/8/2012）的主题。