常用sql(1.分组取最新的 N 条数据; 2.删除重复数据 )

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条数据

 删除重复数据

DELETE
FROM
    yibai_walmart_listing
WHERE
    account_id = {$account_id}
AND item_id IN (
    SELECT
        *
    FROM
        (
            SELECT
                item_id
            FROM
                yibai_walmart_listing
            WHERE
                account_id = {$account_id}
            GROUP BY
                item_id
            HAVING
                COUNT(*) > 1
        ) AS u
)
AND id NOT IN (
    SELECT
        *
    FROM
        (
            SELECT
                MAX(id) AS id
            FROM
                yibai_walmart_listing
            WHERE
                account_id = {$account_id}
            GROUP BY
                item_id
            HAVING
                COUNT(*) > 1
        ) AS u2
)

 2.两个表更新同步

UPDATE yibai_amazon_listing_alls AS t
INNER JOIN yibai_amazon_listings_all_raw2 AS a ON t.account_id = a.account_id 
AND t.seller_sku = a.seller_sku 
AND t.fulfillment_channel = a.fulfillment_channel 
SET t.price = a.price,
{ $set_column } t.quantity = a.quantity,
t.item_name = a.item_name,
t.pending_quantity = a.pending_quantity,
t.fulfillment_channel = a.fulfillment_channel,
t.real_status =
IF
    ( a.fulfillment_channel = 'DEF' && a.quantity IS NOT NULL, 0, t.real_status ),
    t.`status` = a.`status`,
    t.update_date = a.create_time 
WHERE
    t.account_id = 100

UPDATE yibai_amazon_listing_alls_addition AS t
INNER JOIN yibai_amazon_listing_alls AS a ON t.account_id = a.account_id AND t.seller_sku = a.seller_sku
SET 
t.item_category_name = IFNULL(a.item_category_name,''),
t.brand = IFNULL(a.brand,''),
t.item_category_id = a.item_category_id,
t.item_point1 = FROM_BASE64(a.item_point1),
t.item_point2 = FROM_BASE64(a.item_point2),
t.item_point3 = FROM_BASE64(a.item_point3),
t.item_point4 = FROM_BASE64(a.item_point4),
t.item_point5 = FROM_BASE64(a.item_point5),
t.brand_update = a.brand_update
WHERE
t.account_id = 100

UPDATE yibai_amazon_listing_alls_addition AS t
INNER JOIN yibai_amazon_category_new AS a ON t.item_category_id = a.node_id 
AND t.item_category_id = a.node_id 
SET t.node_id_aum = a.node_num,
t.node_path_id = ( CASE WHEN a.node_num = 1 THEN a.node_path_id ELSE t.node_path_id END ),
t.node_path_name = ( CASE WHEN a.node_num = 1 THEN a.node_path ELSE t.node_path_name END ) 
WHERE
   t.account_id = 100 
   AND a.site = 'ca'

3.两个表插入同步  查找yibai_amazon_listing_alls 表 新增的 status=1的数据  在yibai_amazon_listing_alls_additon里面没有  则插入到 addition里面  里面有解析base64 如果解析出 显示的是 BLOB 则 用   CONVERT(FROM_BASE64(item_name) USING utf8) as item_name,

INSERT INTO yibai_amazon_listing_alls_addition (
   account_id,
   seller_sku,
   asin,
   item_category_id,
   item_category_name,
   item_point1,
   item_point2,
   item_point3,
   item_point4,
   item_point5,
   brand,
   create_date,
   update_date 
)
SELECT
   t.account_id,
   t.seller_sku,
   t.asin1,
   t.item_category_id,
   ifnull( t.item_category_name, '' ) AS item_category_name,
   FROM_BASE64(t.item_point1),
   FROM_BASE64(t.item_point2),
   FROM_BASE64(t.item_point3),
   FROM_BASE64(t.item_point4),
   FROM_BASE64(t.item_point5),
   ifnull( t.brand, '' ) AS brand,
   t.create_date,
   t.update_date 
FROM
   yibai_amazon_listing_alls t
   LEFT JOIN yibai_amazon_listing_alls_addition a ON t.account_id = a.account_id 
   AND t.seller_sku = a.seller_sku 
WHERE
   t.account_id = 100
   AND t.`status` = 1 
   AND a.seller_sku IS NULL




SELECT
	account_id,
	seller_sku,
	CONVERT(FROM_BASE64(item_name) USING utf8) as item_name,
	CONVERT(FROM_BASE64(item_description) USING utf8) as item_description,
	FROM_BASE64(item_point1),
	FROM_BASE64(item_point2),
	FROM_BASE64(item_point3),
	FROM_BASE64(item_point4)
FROM
	yibai_amazon_listing_alls 
WHERE
	account_id = 7 
	LIMIT 100

 4.如果么个字段等于1则多更新几个字段

UPDATE yibai_amazon_listing_alls_addition AS t
INNER JOIN yibai_amazon_category_new AS a ON t.item_category_id = a.node_id 
AND t.item_category_id = a.node_id 
SET t.node_id_aum = a.node_num,
t.node_path_id = ( CASE WHEN a.node_num = 1 THEN a.node_path_id ELSE t.node_path_id END ),
t.node_path_name = ( CASE WHEN a.node_num = 1 THEN a.node_path ELSE t.node_path_name END ) 
WHERE
    t.account_id = 100 
    AND a.site = 'ca'

 5.mysql 字符串替换 https://www.amazon.mx/dp/ 替换为 https://www.amazon.com.mx/gp/product/

UPDATE yibai_amazon_listing_crawler SET amazon_url=REPLACE(amazon_url,'https://www.amazon.mx/dp/','https://www.amazon.com.mx/gp/product/')

6.如果 node_id_aum >1 并且 node_path_id 字符串包含了 >> 则把 node_path_id 和 node_path_name 互换

UPDATE yibai_amazon_listing_alls_addition 
SET node_path_id=(@temp1:=node_path_id),node_path_id = ( CASE WHEN (node_id_aum > 1 && LOCATE('>>',node_path_id)>0) THEN  node_path_name  ELSE node_path_id END ),
node_path_name = ( CASE WHEN (node_id_aum > 1 && LOCATE('>>',@temp1)>0) THEN  @temp1 ELSE node_path_name END ) 
WHERE
    account_id = 100