Oracel SQL 记录

最新推荐文章于 2025-01-11 10:31:56 发布

原创最新推荐文章于 2025-01-11 10:31:56 发布 · 647 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #join #c

oracle 专栏收录该内容

100 篇文章

订阅专栏

本文介绍了一种根据库存量对热销商品进行排序的算法，通过SQL查询实现，旨在优化库存管理和销售策略。

1. 根据占用库存排序热销商品

select goods.id,
               nvl(goods.name, ''),
               nvl(goods.price, 0),
               TO_CHAR(SUBSTR(goods.goodsimageliststore,
                              (INSTR(goods.goodsimageliststore,
                                     'sourceGoodsImagePath',
                                     1,
                                     1) + 23),
                              (INSTR(goods.goodsimageliststore,
                                     'thumbnailGoodsImagePath',
                                     1,
                                     1) - INSTR(goods.goodsimageliststore,
                                                 'sourceGoodsImagePath',
                                                 1,
                                                 1) - 26))) imageUrl,
               temp.freezeStore
          from (select tg.id, sum(nvl(tp.freezeStore, 0)) as freezeStore
                  from tsp_goods tg
                  left join tsp_product tp
                    on tp.goods_id = tg.id
                 where tg.isdelete = 0
                   and tg.isMarketable = 1
                   and tg.store_id = 'f9895ca72f8c31fc012f94e06f5e0249'
                 group by tg.id
                 order by freezeStore desc) temp
          join tsp_goods goods
            on temp.id = goods.id