如何修改N1QL语句以获取最小env值且对应最大createdTs的记录

我明白你的需求了——现在你需要从数据里筛选出每个partnerApp对应的最小env值的记录，而且如果同一个最小env下有多条记录，得挑其中createdTs最新的那条。原来的语句用MIN([t5.data.env, ...])只能拿到按env排序最小的那条，但没法保证是该env下时间戳最大的，咱们来调整一下逻辑。

这里有两种可行的方案，你可以根据自己的Couchbase版本和偏好选择：

方案一：用多CTE分步筛选（兼容大部分版本） #

先分步锁定最小env，再锁定该env下的最大时间戳，最后关联出完整记录：

WITH ct1 AS (
    -- 第一步：按每个partnerApp分组，算出对应的最小env值
    SELECT t4.uuid AS partnerAppId,
           MIN(t5.data.env) AS min_env
    FROM api_external AS t4
    JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
    WHERE t4.type = "partnerApp"
      AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
      AND t5.type = "integration"
      AND t5.data.partnerAppId IS NOT NULL
    GROUP BY t4.uuid
),
ct2 AS (
    -- 第二步：基于最小env，算出该分组下的最大createdTs
    SELECT t5.data.partnerAppId AS partnerAppId,
           MAX(t5.createdTs) AS max_createdTs
    FROM api_external AS t5
    JOIN ct1 ON t5.data.partnerAppId = ct1.partnerAppId AND t5.data.env = ct1.min_env
    WHERE t5.type = "integration"
    GROUP BY t5.data.partnerAppId, t5.data.env
),
ct3 AS (
    -- 第三步：关联所有表，精准筛选符合条件的记录
    SELECT t4.data.appName AS name,
           t4.uuid AS id,
           t4.data.description,
           t5.data.env,
           t5.data.productStatus,
           t4.data.fourthParty AS dcrFlag,
           t5.createdTs
    FROM api_external AS t4
    JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
    JOIN ct1 ON t4.uuid = ct1.partnerAppId AND t5.data.env = ct1.min_env
    JOIN ct2 ON t4.uuid = ct2.partnerAppId AND t5.createdTs = ct2.max_createdTs
    WHERE t4.type = "partnerApp"
      AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
      AND t5.type = "integration"
)
-- 最终查询和你原来的逻辑一致
SELECT ct3.name,
       ct3.id,
       ct3.description,
       ct3.env,
       ct3.dcrFlag,
       ct3.createdTs,
       (SELECT api_external.data.displayName AS productName,
               uuid AS productId
        FROM api_external USE KEYS (ARRAY "product::" || v FOR v IN OBJECT_NAMES(ct3.productStatus) END)
       ) AS ProductDetails
FROM ct3

方案二：用窗口函数（更简洁，适合Couchbase 5.5+） #

利用ROW_NUMBER()窗口函数，直接按规则排序并取第一条记录，逻辑更直观：

WITH ranked_records AS (
    SELECT t4.data.appName AS name,
           t4.uuid AS id,
           t4.data.description,
           t5.data.env,
           t5.data.productStatus,
           t4.data.fourthParty AS dcrFlag,
           t5.createdTs,
           -- 按partnerApp分组，先按env升序（最小env排前面），再按时间戳降序（最新排前面）
           ROW_NUMBER() OVER (PARTITION BY t4.uuid ORDER BY t5.data.env ASC, t5.createdTs DESC) AS rn
    FROM api_external AS t4
    JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
    WHERE t4.type = "partnerApp"
      AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
      AND t5.type = "integration"
      AND t5.data.partnerAppId IS NOT NULL
)
-- 取每个分组里的第一条记录（就是最小env+最大时间戳的那条）
SELECT name,
       id,
       description,
       env,
       dcrFlag,
       createdTs,
       (SELECT api_external.data.displayName AS productName,
               uuid AS productId
        FROM api_external USE KEYS (ARRAY "product::" || v FOR v IN OBJECT_NAMES(productStatus) END)
       ) AS ProductDetails
FROM ranked_records
WHERE rn = 1

两种方案都能满足你的需求，窗口函数的写法更简洁易读，如果你用的Couchbase版本支持，优先选这个。

备注：内容来源于stack exchange，提问作者hemanth