CTE表示一列中具有最大值的表中的行的逻辑表 [英] CTE to represent a logical table for the rows in a table which have the max value in one column

问题描述

我有一个只插入数据库，其中记录不是物理上更新的，而是通过添加具有CRUD值的新记录进行逻辑更新，其携带更大的序列。在这种情况下，seq（序列）列更符合您可以考虑的主键，但id是记录的逻辑标识符。在下面的示例中，

这是表的实际表示：

 
 seq id name | CRUD | 
 ---- | ----- | -------- | ------ | 
 1 | 10 |约翰C | 
 2 | 10 |乔| U | 
 3 | 11 |肯特| C | 
 4 | 12 | katie | C | 
 5 | 12 |起诉| U | 
 6 | 13 |吉尔| C | 
 7 | 14 |帐单| C | 
 

这是表的逻辑表示，考虑到最近的记录：

 
 seq id name | CRUD | 
 ---- | ----- | -------- | ------ | 
 2 | 10 |乔| U | 
 3 | 11 |肯特| C | 
 5 | 12 |起诉| U | 
 6 | 13 |吉尔| C | 
 7 | 14 |帐单| C | 
 

为了例如，为id = 12的人检索最近的记录，我现在会这样做：

  SELECT 
 * 
 FROM 
 PEOPLE P 
 WHERE 
 P.ID = 12 
 AND 
 P.SEQ =（
 SELECT 
 MAX（P1.SEQ）
 FROM 
 PEOPLE P1 
 WHERE P.ID = 12 
）
  

...我会收到这一行： / p>

 
 seq id name | CRUD | 
 ---- | ----- | -------- | ------ | 
 5 | 12 |起诉| U | 
 

我想做的是这样的：

  WITH 
 NEW_P 
 AS 
（
  - 代表所有最近的记录
  - 代表任何给定的id，最新序列
）
 
 SELECT 
 * 
 FROM 
 NEW_P P2 
 WHERE 
 P2.ID = 12 
  

使用子查询的第一个SQL示例已经适用于我们。

问题：当需要利用表的最新逻辑视图时，如何利用CTE来简化谓词。在本质上，我不想每次单独列出一个子查询，我想得到最近的记录。我宁愿定义一个CTE，并在任何后续谓词中进行杠杆化。

当我正在使用DB2时，我正在寻找数据库不可知的解决方案。

解决方案

这是一个明确的例子用于窗口（或 OLAP ）函数，这些函数都受到所有现代SQL数据库的支持。例如：

  WITH 
 ORD_P 
 AS 
（
 SELECT p。 *，ROW_NUMBER（）OVER（PARTITION BY id ORDER BY seq DESC）rn 
从人p 
）
，
 NEW_P 
 AS 
（
 SELECT * from ORD_P 
 WHERE rn = 1 
）
 SELECT 
 * 
 FROM 
 NEW_P P2 
 WHERE 
 P2 .ID = 12 
  

PS。没有测试。您可能需要明确列出CTE子句中的所有列。

I have an "insert only" database, wherein records aren't physically updated, but rather logically updated by adding a new record, with a CRUD value, carrying a larger sequence. In this case, the "seq" (sequence) column is more in line with what you may consider a primary key, but the "id" is the logical identifier for the record. In the example below,

This is the physical representation of the table:

seq   id    name   | CRUD |
----|-----|--------|------|
1   | 10  | john   | C    |
2   | 10  | joe    | U    |
3   | 11  | kent   | C    |
4   | 12  | katie  | C    |
5   | 12  | sue    | U    |
6   | 13  | jill   | C    |
7   | 14  | bill   | C    |

This is the logical representation of the table, considering the "most recent" records:

seq   id    name   | CRUD |
----|-----|--------|------|
2   | 10  | joe    | U    |
3   | 11  | kent   | C    |
5   | 12  | sue    | U    |
6   | 13  | jill   | C    |
7   | 14  | bill   | C    |

In order to, for instance, retrieve the most recent record for the person with id=12, I would currently do something like this:

SELECT 
    *
FROM
    PEOPLE P
WHERE       
    P.ID = 12
AND
    P.SEQ = (
        SELECT
            MAX(P1.SEQ)
        FROM
            PEOPLE P1
        WHERE P.ID = 12
    )

...and I would receive this row:

seq   id    name   | CRUD |
----|-----|--------|------|
5   | 12  | sue    | U    |

What I'd rather do is something like this:

WITH
    NEW_P
AS
(
    --CTE representing all of the most recent records
    --i.e. for any given id, the most recent sequence
)

SELECT 
    *
FROM
    NEW_P P2
WHERE       
    P2.ID = 12

The first SQL example using the the subquery already works for us.

Question: How can I leverage a CTE to simplify our predicates when needing to leverage the "most recent" logical view of the table. In essence, I don't want to inline a subquery every single time I want to get at the most recent record. I'd rather define a CTE and leverage that in any subsequent predicate.

P.S. While I'm currently using DB2, I'm looking for a solution that is database agnostic.

解决方案

This is a clear case for window (or OLAP) functions, which are supported by all modern SQL databases. For example:

WITH
    ORD_P
AS
(
   SELECT p.*, ROW_NUMBER() OVER ( PARTITION BY id ORDER BY seq DESC) rn
   FROM people p
)
,
    NEW_P
AS 
(
    SELECT * from ORD_P
    WHERE rn = 1
)
SELECT 
    *
FROM
    NEW_P P2
WHERE       
    P2.ID = 12

PS. Not tested. You may need to explicitly list all columns in the CTE clauses.

这篇关于CTE表示一列中具有最大值的表中的行的逻辑表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！