常用sql

1.查询 yibai_amazon_report_zn_info 按 planid 分组 查询 最新的 N 条数据 

CREATE TABLE `yibai_amazon_report_zn_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `accountid` int(11) NOT NULL DEFAULT '0' COMMENT '账户ID',
  `planid` int(11) NOT NULL DEFAULT '0' COMMENT '计划ID',
  `taskid` varchar(255) NOT NULL DEFAULT '' COMMENT '任务ID',
  `taskrecordid` bigint(20) NOT NULL DEFAULT '0' COMMENT '任务记录ID',
  `sellerid` varchar(100) NOT NULL DEFAULT '' COMMENT '店铺的merchant_id',
  `sitename` varchar(50) NOT NULL DEFAULT '' COMMENT '站点名称',
  `sitecode` varchar(10) NOT NULL DEFAULT '' COMMENT '站点编码',
  `rpaid` int(11) NOT NULL DEFAULT '0' COMMENT 'rpaid',
  `taskresulttype` varchar(50) NOT NULL DEFAULT '' COMMENT '任务状态',
  `errormsg` varchar(300) NOT NULL DEFAULT '' COMMENT '错误',
  `url` varchar(5000) NOT NULL DEFAULT '' COMMENT '下载链接',
  `is_down` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0:未下载处理 1:已经处理',
  `create_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `update_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
  `model` tinyint(1) NOT NULL DEFAULT '0' COMMENT '类型  0：日期范围报告 1：结算一览（上个结算周期）',
  `num` int(11) NOT NULL DEFAULT '0' COMMENT '条数',
  `is_true` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否正确',
  `is_retry` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0:正常 1:任务重试',
  PRIMARY KEY (`id`),
  KEY `idx_taskid` (`taskid`) USING BTREE,
  KEY `idx_planid` (`planid`) USING BTREE,
  KEY `idx_is_down` (`is_down`) USING BTREE,
  KEY `idx_model` (`model`),
  KEY `idx_is_true` (`is_true`)
) ENGINE=InnoDB AUTO_INCREMENT=1593825 DEFAULT CHARSET=utf8 COMMENT='紫鸟报告列表';

SELECT
    ranked.planid,
    ranked.id,
    ranked.sellerid,
    ranked.sitecode,
    ranked.accountid,
    ranked.taskrecordid,
    ranked.create_at,
    ranked.is_down,
    ranked.rank
FROM
    (
        SELECT
            tp.planid,
            tp.id,
            tp.sellerid,
            tp.sitecode,
            tp.accountid,
            tp.taskrecordid,
            tp.create_at,
            tp.is_down,
            @rank :=
        IF (
            @curr_cur = tp.planid,
            @rank + 1,
            1
        ) AS rank,
        @curr_cur := tp.planid AS dummy
    FROM
        yibai_amazon_report_zn_info tp,
        (
            SELECT
                @rank := 0 ,@curr_cur := 0
        ) temp
    WHERE
        tp.create_at > '2022-08-01'
    AND is_down = 3 
    ORDER BY
        tp.planid,
        id DESC
    ) AS ranked
WHERE
    ranked.rank <= 5 //按 planid 分组，取每组最新的5条数据