每组取前N条”/“Top-1 per group”/“greatest‑N‑per‑group

CREATE TABLE lock_stock_days_record (
id bigint NOT NULL AUTO_INCREMENT,
product_id bigint unsigned NOT NULL DEFAULT '0',
product_size_id bigint unsigned NOT NULL DEFAULT '0',
lock_stock_days int unsigned NOT NULL COMMENT '锁仓时间',
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at datetime DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
KEY idx_record (product_id,product_size_id,created_at)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
这是锁仓配置的记录表，每次变动某个商品的锁仓时间，都会记录，通过最小于一个时间点，id最大的数据，定位某个商品的锁仓天数

CREATE TABLE ai_match_product_order (
id bigint NOT NULL AUTO_INCREMENT COMMENT '订单id',
order_sn varchar(64) DEFAULT NULL COMMENT '订单编号',
product_id bigint DEFAULT NULL COMMENT '商品id',
product_title varchar(500) DEFAULT NULL COMMENT '商品名称',
product_picture varchar(1000) DEFAULT NULL COMMENT '商品封面图',
user_id bigint DEFAULT NULL COMMENT '用户id',
receive_time datetime DEFAULT NULL COMMENT '确认收货时间',
nft_product_size_id bigint DEFAULT '0' COMMENT 'NFT商品款id',
PRIMARY KEY (id),
) ENGINE=InnoDB AUTO_INCREMENT=5115519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
这是订单表

我想查询一个订单，有多少天的锁仓时间，就要用通过，product_id和nft_product_size_id，再通过锁仓表created_at小于等于receive_time，id最大的一条数据，拿到锁仓天数，这种查询，一个sql能查出来吗？

思考中....

可以，一个 SQL 就能查出来。给你两种写法（MySQL 8.0）：

1. 查单个订单的锁仓天数（用相关子查询，最简洁）

SELECT
  o.id,
  o.order_sn,
  (
    SELECT r.lock_stock_days
    FROM lock_stock_days_record r
    WHERE r.product_id = o.product_id
      AND r.product_size_id = o.nft_product_size_id
      AND r.created_at <= o.receive_time
      AND r.deleted_at IS NULL
    ORDER BY r.created_at DESC, r.id DESC
    LIMIT 1
  ) AS lock_stock_days
FROM ai_match_product_order o
WHERE o.id = ?;  -- 指定订单ID

2. 批量查所有订单（或按条件筛选订单）

SELECT
	order_id,
	product_id,
	order_sn,
	lock_stock_days 
FROM
	(
	SELECT
		o.id AS order_id,
		o.product_id,
		o.order_sn,
		r.lock_stock_days,
		ROW_NUMBER() OVER ( PARTITION BY o.id ORDER BY r.created_at DESC, r.id DESC ) AS rn 
	FROM
		ai_match_product_order o
		LEFT JOIN lock_stock_days_record r ON r.product_id = o.product_id 
		AND r.product_size_id = o.nft_product_size_id 
		AND r.created_at <= o.receive_time 
		AND r.deleted_at IS NULL 
	WHERE
		o.product_id = 1016691 
		AND o.product_type = 'NFT' 
		AND o.STATUS = 2 
-- 		AND r.lock_stock_days IS NOT NULL
	) t 
WHERE
	t.rn = 1;

说明与注意

• 挑选规则：在 created_at <= receive_time 的记录中，选 created_at 最新、若并列则 id 最大的那条。
• 软删处理：加了 r.deleted_at IS NULL 条件。
• 如果某订单没有匹配记录，结果里的 lock_stock_days 会是 NULL。
• 你已有索引 KEY idx_record (product_id, product_size_id, created_at)。InnoDB 二级索引会隐式包含主键 id，配合上面的 ORDER BY created_at DESC, id DESC 能走索引范围反向扫描，通常无需 filesort，性能可观。若你的数据/查询模式特殊，可考虑显示建 (product_id, product_size_id, created_at, id) 索引，但通常没必要。

如果我要知道这个东西，有没有解锁，就是判断receive_time加上对应的lock_stock_days，和现在比较，大于等于当前就是还锁着，小于就是已经过了锁仓期。单个和批量查要怎么写？批量还要过滤出已过锁仓期的又要怎么写？

思考中....

可以，一条 SQL 就能算出“是否解锁”。给你单个订单、批量查询、以及仅筛选“已过锁仓期”的三种写法（MySQL 8.0）。

说明

• 判定规则：unlock_time = receive_time + INTERVAL lock_stock_days DAY。若 unlock_time >= NOW() 则“还锁着”；若 unlock_time < NOW() 则“已过锁仓期”。
• 若某订单找不到锁仓记录：常见做法当作 0 天（立即解锁）。示例里用 COALESCE(..., 0) 处理；如果你要当作“未知/保持锁定”，把 COALESCE 去掉即可。
• 软删：按你的表，加了 r.deleted_at IS NULL。
• 索引已足够：你的 idx_record(product_id, product_size_id, created_at) 能支持 ORDER BY created_at DESC, id DESC 的反向范围扫描。

单个订单（相关子查询，最简）
适合 MySQL 5.7/8.0，易读；每单只扫一次索引。

SELECT
  o.id,
  o.order_sn,
  o.receive_time,
  -- 取到的锁仓天数（没有则为 0）
  COALESCE((
    SELECT r.lock_stock_days
    FROM lock_stock_days_record r
    WHERE r.product_id = o.product_id
      AND r.product_size_id = o.nft_product_size_id
      AND r.created_at <= o.receive_time
      AND r.deleted_at IS NULL
    ORDER BY r.created_at DESC, r.id DESC
    LIMIT 1
  ), 0) AS lock_stock_days,
  -- 解锁时间
  TIMESTAMPADD(
    DAY,
    COALESCE((
      SELECT r.lock_stock_days
      FROM lock_stock_days_record r
      WHERE r.product_id = o.product_id
        AND r.product_size_id = o.nft_product_size_id
        AND r.created_at <= o.receive_time
        AND r.deleted_at IS NULL
      ORDER BY r.created_at DESC, r.id DESC
      LIMIT 1
    ), 0),
    o.receive_time
  ) AS unlock_time,
  -- 是否还锁着（含等号：等于现在也算锁着）
  CASE
    WHEN o.receive_time IS NULL THEN NULL
    WHEN TIMESTAMPADD(
           DAY,
           COALESCE((
             SELECT r.lock_stock_days
             FROM lock_stock_days_record r
             WHERE r.product_id = o.product_id
               AND r.product_size_id = o.nft_product_size_id
               AND r.created_at <= o.receive_time
               AND r.deleted_at IS NULL
             ORDER BY r.created_at DESC, r.id DESC
             LIMIT 1
           ), 0),
           o.receive_time
         ) >= NOW()
      THEN 1
    ELSE 0
  END AS is_locked
FROM ai_match_product_order o
WHERE o.id = ?;  -- 传入订单ID

注：为避免重复子查询、在 MySQL 8.0.14+ 可以改用“派生表引用外层”写法（类似 LATERAL），把子查询写到 LEFT JOIN 里，只算一次。

批量查询（窗口函数，选每单的 Top-1 配置）

SELECT
  x.id AS order_id,
  x.order_sn,
  x.receive_time,
  x.lock_stock_days,
  TIMESTAMPADD(DAY, COALESCE(x.lock_stock_days, 0), x.receive_time) AS unlock_time,
  CASE
    WHEN x.receive_time IS NULL THEN NULL
    WHEN TIMESTAMPADD(DAY, COALESCE(x.lock_stock_days, 0), x.receive_time) >= NOW() THEN 1
    ELSE 0
  END AS is_locked
FROM (
  SELECT
    o.id,
    o.order_sn,
    o.receive_time,
    r.lock_stock_days,
    ROW_NUMBER() OVER (
      PARTITION BY o.id
      ORDER BY r.created_at DESC, r.id DESC
    ) AS rn
  FROM ai_match_product_order o
  LEFT JOIN lock_stock_days_record r
    ON r.product_id = o.product_id
   AND r.product_size_id = o.nft_product_size_id
   AND r.created_at <= o.receive_time
   AND r.deleted_at IS NULL
  -- 可加筛选订单条件，例如 WHERE o.id IN (...)
) x
WHERE x.rn = 1;

批量只要“已过锁仓期”的订单
在上面批量查询外再包一层，按 unlock_time < NOW() 过滤即可。

SELECT
	* 
FROM
	(
	SELECT
		x.id AS order_id,
		x.order_sn,
		x.receive_time,
		x.lock_stock_days,
		TIMESTAMPADD( DAY, COALESCE ( x.lock_stock_days, 0 ), x.receive_time ) AS unlock_time,
	CASE
			
			WHEN x.receive_time IS NULL THEN
		NULL 
			WHEN TIMESTAMPADD( DAY, COALESCE ( x.lock_stock_days, 0 ), x.receive_time ) >= NOW() THEN
			1 ELSE 0 
		END AS is_locked 
	FROM
		(
		SELECT
			o.id,
			o.order_sn,
			o.receive_time,
			r.lock_stock_days,
			ROW_NUMBER() OVER ( PARTITION BY o.id ORDER BY r.created_at DESC, r.id DESC ) AS rn 
		FROM
			ai_match_product_order o
			LEFT JOIN lock_stock_days_record r ON r.product_id = o.product_id 
			AND r.product_size_id = o.nft_product_size_id 
			AND r.created_at <= o.receive_time 
			AND r.deleted_at IS NULL 
		WHERE
			o.product_id = 1016691 
			AND o.product_type = 'NFT' 
			AND o.STATUS = 2 
			AND r.lock_stock_days IS NOT NULL
		) x 
	WHERE
		x.rn = 1 
	) t 
WHERE
	t.unlock_time < NOW();-- 仅已过锁仓期
      ON r.product_id = o.product_id
     AND r.product_size_id = o.nft_product_size_id
     AND r.created_at <= o.receive_time
     AND r.deleted_at IS NULL
    -- 可加订单筛选
  ) x
  WHERE x.rn = 1
) t
WHERE t.unlock_time < NOW();   -- 仅已过锁仓期

可选优化/变体

• 若你希望“没有配置记录”的订单算“未知而非解锁”，把 COALESCE(..., 0) 去掉，并把过滤条件改成 t.unlock_time IS NOT NULL AND t.unlock_time < NOW()。
• 如果批量只查近期订单，可在最外层加条件限制行数；或对订单（receive_time）建索引以减少 JOIN 行数。