如何成功实现更新整张表的商品涨跌数据，SQL语句是什么
发布时间：2023-05-11 14:09:34 所属栏目：MsSql教程 来源：
导读：这篇文章给大家分享的是“如何实现更新整张表的商品涨跌数据，SQL语句是什么”，对大家学习和理解有一定的参考价值和帮助，有这方面学习需要的朋友，接下来就跟随小编一起学习一下吧。
问题场景
各
问题场景
各
|
这篇文章给大家分享的是“如何实现更新整张表的商品涨跌数据，SQL语句是什么”，对大家学习和理解有一定的参考价值和帮助，有这方面学习需要的朋友，接下来就跟随小编一起学习一下吧。 问题场景 各大平台店铺的三项评分（物流、服务、商品）变化情况； 商品每日价格的变化记录； 股票的实时涨跌浮； 复现场景 表：主键ID，商品编号，记录时的时间，记录时的价格，创建时间。 问题：获取每个商品每次的变化情况（涨跌幅、涨跌率）。 解决思路 1、要想高效率的更新涨跌，就肯定不能是逐条数据更新，要通过自连表建立起对应关系，将每一条数据关联到上一次的价格数据。 2、由于数据库非常庞大，所以可能存在很多垃圾数据，就比如说相关的字段值为NULL或者非有效值的，这些数据要先排除掉。 SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL; 3、然后在获取每条数据的上一条数据，同样也要先排除掉垃圾数据。 SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id; 4、获取到上一条数据后，获取上条数据对应的商品价格。 SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM ( SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id ) AS tmp_ab LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id; 5、获取到上条数据以及对应的价格后，开始进行计算，获取到最终的结果。 SELECT *, (CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2))) AS '涨跌幅', ROUND((CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2)))/CONVERT(last_price, DECIMAL(10,2)), 2) AS '涨跌率' FROM ( SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM ( SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id ) AS tmp_ab LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id ) AS tmp 解决方案 -- 创建表SQL CREATE TABLE `test_goods_price_change` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `goods_code` varchar(50) NOT NULL COMMENT '商品编码', `goods_date` int(11) NOT NULL COMMENT '记录时的时间', `goods_price` decimal(10,2) NOT NULL COMMENT '记录时的价格', `created_at` int(11) NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=utf8mb4; -- 获取涨跌浮SQL SELECT *, (CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2))) AS '涨跌幅', ROUND((CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2)))/CONVERT(last_price, DECIMAL(10,2)), 2) AS '涨跌率' FROM ( SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM ( SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id ) AS tmp_ab LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id ) AS tmp （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
