Azure Cosmos DB NoSQL API：从重复记录中获取最新创建日期

每条记录代表一个菜品的每日销售数据，用户可能重复上传同一菜品的每日销售记录，最新上传的通常是修正后的销售数据，我们只关注**最新创建日期（即最新上传）**的记录。

[{
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Coffee",
    "popularity": 1600,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T01:00:00.000Z"
  },
  {
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Coffee",
    "popularity": 1760,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T02:00:00.000Z"
  },
  {
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Coffee",
    "popularity": 1953,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T03:00:00.000Z"
  },
  {
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Tea",
    "popularity": 976,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T01:00:00.000Z"
  },
  {
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Tea",
    "popularity": 1109,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T02:00:00.000Z"
  },
  {
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Tea",
    "popularity": 1422,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T03:00:00.000Z"
  }
]

[{
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Coffee",
    "popularity": 1953,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T03:00:00.000Z"
  },
  {
    "tenantId": "abcd-efgh-ijkl-mnop",
    "menuItemName": "Tea",
    "popularity": 1422,
    "itemFoodCost": 2.5,
    "itemSellPrice": 6.5,
    "dateSold": "2025-02-24",
    "dateCreated": "2025-02-24T03:00:00.000Z"
  }
]

子查询方案（返回0条数据） #

尝试了以下查询，预期得到目标结果，但返回0条数据：

SELECT *
FROM c
WHERE 
  c.tenantId = "abcd-efgh-ijkl-mnop" AND
  c.dateCreated = (
    SELECT MAX(sub.dateCreated)
    FROM c AS sub
    WHERE 
      sub.menuItemName = c.menuItemName AND 
      sub.tenantId = c.tenantId
  )

问题原因：

1. dateCreated是带时区的字符串，直接用=比较可能存在精度或格式匹配问题；
2. 子查询未限定dateSold，会跨日期取最大dateCreated，不符合「每日销售数据的最新上传记录」的需求。

JOIN方案（返回所有重复记录） #

之后尝试使用JOIN查询，但c.latestDateCreated字段未显示，且返回了所有重复记录：

SELECT
  c.menuItemName,
  c.dateCreated,
  c.dateSold,
  c.latestDateCreated
FROM
  c
JOIN (
  SELECT
    sub.menuItemName,
    sub.dateSold,
    MAX(sub.dateCreated) AS latestDateCreated
  FROM
    c AS sub
  WHERE
    sub.tenantId = "abcd-efgh-ijkl-mnop"
  GROUP BY
    sub.menuItemName,
    sub.dateSold,
    sub.latestDateCreated
)
WHERE
  c.tenantId = "abcd-efgh-ijkl-mnop"

问题原因：

1. JOIN子句缺少关联条件，相当于笛卡尔积，导致返回所有记录；
2. GROUP BY中包含聚合字段latestDateCreated，分组逻辑失效，每个记录单独成组；
3. 主查询错误地用c.latestDateCreated引用子查询字段，需用子查询别名引用。

方案1：使用IN子查询限定关联条件 #

SELECT *
FROM c
WHERE 
  c.tenantId = "abcd-efgh-ijkl-mnop" AND
  (c.menuItemName, c.dateSold, c.dateCreated) IN (
    SELECT 
      sub.menuItemName, 
      sub.dateSold, 
      MAX(sub.dateCreated)
    FROM c AS sub
    WHERE sub.tenantId = "abcd-efgh-ijkl-mnop"
    GROUP BY sub.menuItemName, sub.dateSold
  )

方案2：使用JOIN添加正确关联条件 #

SELECT c.*
FROM c
JOIN (
  SELECT
    sub.menuItemName,
    sub.dateSold,
    MAX(sub.dateCreated) AS latestDateCreated
  FROM c AS sub
  WHERE sub.tenantId = "abcd-efgh-ijkl-mnop"
  GROUP BY sub.menuItemName, sub.dateSold
) AS latest
ON c.menuItemName = latest.menuItemName 
   AND c.dateSold = latest.dateSold 
   AND c.dateCreated = latest.latestDateCreated
WHERE c.tenantId = "abcd-efgh-ijkl-mnop"

以上两个方案均可准确筛选出每个菜品每日销售记录中最新上传的那条数据，完全匹配目标结果要求。

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