1️⃣ 通用 SQL 模板（支持分页 + 总条数）

SELECT
    ai.id AS id,
    ai.title AS title,
    CAST(ai.update_count AS INTEGER) AS update_count,   -- SQLite: CAST, MySQL/PostgreSQL 可去掉
    CASE WHEN awh.id IS NOT NULL THEN 1 ELSE 0 END AS is_watched,  -- PostgreSQL 可以改 TRUE/FALSE
    COALESCE(awh.user_id, '') AS user_id,
    {convert_update_time} AS update_time,               -- 不同数据库时间函数不同
    {convert_watch_time} AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count                      -- 窗口函数返回总条数
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = :user_id
ORDER BY ai.update_time DESC
LIMIT :page_size OFFSET (:page_index - 1) * :page_size;

2️⃣ 数据库差异

数据库 时间转换函数 布尔类型 注释
SQLite datetime(ai.update_time, 'unixepoch', 'localtime') 0/1 Unix 时间戳（秒）
MySQL FROM_UNIXTIME(ai.update_time) 0/1 Unix 时间戳（秒）
PostgreSQL to_timestamp(ai.update_time) TRUE/FALSE Unix 时间戳（秒）

例：SQLite 具体写法

SELECT
    ai.id AS id,
    ai.title AS title,
    CAST(ai.update_count AS INTEGER) AS update_count,
    CASE WHEN awh.id IS NOT NULL THEN 1 ELSE 0 END AS is_watched,
    COALESCE(awh.user_id, '') AS user_id,
    datetime(ai.update_time, 'unixepoch', 'localtime') AS update_time,
    CASE WHEN awh.watched_time IS NOT NULL THEN datetime(awh.watched_time, 'unixepoch', 'localtime') ELSE '' END AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
ORDER BY ai.update_time DESC
LIMIT 20 OFFSET 20;

MySQL 8+ 具体写法

SELECT
    ai.id AS id,
    ai.title AS title,
    ai.update_count AS update_count,
    CASE WHEN awh.id IS NOT NULL THEN 1 ELSE 0 END AS is_watched,
    COALESCE(awh.user_id, '') AS user_id,
    FROM_UNIXTIME(ai.update_time) AS update_time,
    CASE WHEN awh.watched_time IS NOT NULL THEN FROM_UNIXTIME(awh.watched_time) ELSE NULL END AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
ORDER BY ai.update_time DESC
LIMIT 20 OFFSET 20;

PostgreSQL 具体写法

SELECT
    ai.id AS id,
    ai.title AS title,
    ai.update_count AS update_count,
    CASE WHEN awh.id IS NOT NULL THEN TRUE ELSE FALSE END AS is_watched,
    COALESCE(awh.user_id, '') AS user_id,
    to_timestamp(ai.update_time) AS update_time,
    CASE WHEN awh.watched_time IS NOT NULL THEN to_timestamp(awh.watched_time) ELSE NULL END AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
ORDER BY ai.update_time DESC
LIMIT 20 OFFSET 20;

3️⃣ 如果不支持窗口函数

对 SQLite <3.25 或 MySQL <8：

SELECT p.*, t.total_count
FROM (
    -- 分页数据
    SELECT ...
    FROM ani_info ai
    LEFT JOIN ani_watch_history awh
           ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
    ORDER BY ai.update_time DESC
    LIMIT 20 OFFSET 20
) AS p
CROSS JOIN (
    -- 总条数
    SELECT COUNT(*) AS total_count
    FROM ani_info ai
    LEFT JOIN ani_watch_history awh
           ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
) AS t;

✅ 总结要点

  1. 优先使用 窗口函数 COUNT(*) OVER()，一条 SQL 返回分页 + 总条数，高效且简单。