Sybase SQL -选择最近的记录，并与上一条记录中的值对齐

-- 假设我们有一个名为orders的表，其中包含order_id, customer_id, order_date等字段
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- 插入一些示例数据
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1, 101, '2021-01-01'),
       (2, 101, '2021-02-01'),
       (3, 102, '2021-01-15'),
       (4, 102, '2021-02-15'),
       (5, 103, '2021-01-10');

WITH ranked_orders AS (
    SELECT order_id, customer_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT ro1.customer_id, ro1.order_date AS current_order_date, ro2.order_date AS previous_order_date
FROM ranked_orders ro1
LEFT JOIN ranked_orders ro2 ON ro1.customer_id = ro2.customer_id AND ro1.rn = ro2.rn + 1
WHERE ro1.rn = 1
ORDER BY ro1.customer_id;

 customer_id | current_order_date | previous_order_date
-------------|-------------------|-------------------
          101 | 2021-02-01        | 2021-01-01
          102 | 2021-02-15        | 2021-01-15
          103 | 2021-01-10        | NULL