SQL视图与连接对比及按自定义Lag排序产品的查询实现咨询

数据表与现有查询 #

-- INIT database
CREATE TABLE Product 
(
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT,
    TimeScale INTEGER,
    CreatedTime Integer
);

INSERT INTO Product(Name, Timescale, CreatedTime) VALUES ("foo", 1, 101);
INSERT INTO Product(Name, Timescale, CreatedTime) VALUES ("bar", 2, 1500);

-- QUERY database
SELECT Name, Timescale, CreatedTime
FROM Product
WHERE (TimeScale = 1 AND CreatedTime >= 100) OR (TimeScale = 2 AND createdTime >= 1000)

每个产品包含名称、TimeScale（时间尺度）和CreatedTime（创建时间）属性，“Lag”值计算规则：

• TimeScale为1时，阈值100，Lag = CreatedTime - 100
• TimeScale为2时，阈值1000，Lag = CreatedTime - 1000

需求：编写按Lag值排序产品的查询，同时了解SQL中视图与连接的差异。

按Lag值排序的查询实现 #

不需要创建视图，直接在SELECT语句中用CASE计算Lag值，再基于该字段排序即可，示例如下：

SELECT 
    Name, 
    TimeScale, 
    CreatedTime,
    CASE 
        WHEN TimeScale = 1 THEN CreatedTime - 100
        WHEN TimeScale = 2 THEN CreatedTime - 1000
    END AS Lag
FROM Product
WHERE 
    (TimeScale = 1 AND CreatedTime >= 100) 
    OR (TimeScale = 2 AND CreatedTime >= 1000)
ORDER BY Lag DESC; -- 改为ASC可实现升序排序

如果这个Lag计算逻辑需要在多个查询中复用，可以创建视图封装逻辑：

CREATE VIEW ProductWithLag AS
SELECT 
    ProductID,
    Name, 
    TimeScale, 
    CreatedTime,
    CASE 
        WHEN TimeScale = 1 THEN CreatedTime - 100
        WHEN TimeScale = 2 THEN CreatedTime - 1000
    END AS Lag
FROM Product;

之后查询视图并排序：

SELECT Name, TimeScale, CreatedTime, Lag
FROM ProductWithLag
WHERE 
    (TimeScale = 1 AND CreatedTime >= 100) 
    OR (TimeScale = 2 AND CreatedTime >= 1000)
ORDER BY Lag;

视图与连接的差异 #

• 核心用途不同

  • 视图：是基于查询结果的虚拟表，用于封装复杂查询逻辑、简化重复查询、控制数据访问范围。它不存储数据，每次查询视图时都会执行底层SQL语句。
  • 连接（JOIN）：用于将多个表或视图的数据按关联条件合并，是构建跨表查询的核心操作，目的是获取分散在不同表中的关联数据。

• 数据处理方式不同

  • 视图：是对已有数据的“包装”，不改变数据存储，仅提供查询入口。
  • 连接：查询时临时将多个数据源的记录匹配组合，生成新的结果集。

• 使用场景不同

  • 视图适合重复使用同一套查询逻辑，或需要对用户隐藏复杂表结构时。
  • 连接适合从多个关联表中获取数据的场景（比如产品表关联分类表获取所属分类）。

内容的提问来源于stack exchange，提问作者nz_21