连表更新 联表更新 排行榜 涨幅

 

-- 新进榜单，涨幅设为9999
-- 查出涨幅

SELECT shop_id,ranking,`change` FROM apprank_ranking_info
WHERE stat_datetime="2024-06-03" AND rank_type = "bestseller" ORDER BY shop_id ;

SELECT shop_id,ranking,`change` FROM apprank_ranking_info
WHERE stat_datetime="2024-06-02" AND rank_type = "bestseller" ORDER BY shop_id ;

SELECT a.shop_id,a.ranking,b.ranking,IF(b.shop_id IS NULL,9999,-a.ranking+b.ranking) AS ret,`change` FROM (

SELECT shop_id,ranking,`change` FROM apprank_ranking_info
WHERE stat_datetime="2024-06-03" AND rank_type = "bestseller" ORDER BY shop_id
) AS a LEFT JOIN
(
SELECT shop_id,ranking FROM apprank_ranking_info
WHERE stat_datetime="2024-06-02" AND rank_type = "bestseller" ORDER BY shop_id
) AS b ON a.shop_id=b.shop_id

;

-- 连表更新涨幅
UPDATE apprank_ranking_info X LEFT JOIN (

SELECT a.shop_id,a.stat_datetime,IF(b.shop_id IS NULL,9999,-a.ranking+b.ranking) AS ret FROM (

SELECT shop_id,ranking,`change` ,stat_datetime FROM apprank_ranking_info
WHERE stat_datetime="2024-06-03" AND rank_type = "bestseller" ORDER BY shop_id
) AS a LEFT JOIN
(
SELECT shop_id,ranking FROM apprank_ranking_info
WHERE stat_datetime="2024-06-02" AND rank_type = "bestseller" ORDER BY shop_id
) AS b ON a.shop_id=b.shop_id

) y ON x.stat_datetime=y.stat_datetime AND x.shop_id=y.shop_id SET x.`change`=IF(y.ret IS NULL,9999,y.ret);

 

 

 

实践：

1、

商品表记录了品牌名，现在需要找回品牌Id

通过品牌名，关联查询更新

UPDATE Goods g LEFT JOIN GoodsBrand b

ON g.brand_name=b.Name

SET g.BrandId=IFNULL(b.Id,0) WHERE 1;

 

 

update visit_copy c left join( SELECT SUM(pv) AS sumpv,uid FROM visit_middle GROUP BY uid ) d on c.uid = d.uid set c.total = d.sumpv;

update A inner join(select id,name from B) c on A.id = c.id set A.name = c.name;

 

翻译

搜索

复制