时间戳

首页 > 解决方案 > 如何增加字母数字值

sql - 如何增加字母数字值

问题描述

我有一个包含 200 多个项目的表。我想列出它们并为每 5 个项目分配一个组代码。例如

GroupCode    Item
GC-01        ITEM-A
GC-01        ITEM-B
GC-01        ITEM-C
GC-01        ITEM-D
GC-01        ITEM-E
GC-02        ITEM-F
GC-02        ITEM-G
GC-02        ITEM-H

每 5 项 GroupCode 应在字母数字代码的数字部分加 1。

SELECT 'GC-01' "GroupCode", ITEM_NUMBER "Item" FROM ITEMS;

这是任何人都可以帮助的吗？

标签： sqloracle

解决方案

A query like the one below should give you a 5 items for each group code. This will only print out group codes from GC-01 to GC-99. If you need a group number higher than 99, you will need to adjust the format mask.

WITH
    items (item)
    AS
        (SELECT 'ITEM-A' FROM DUAL
         UNION ALL
         SELECT 'ITEM-B' FROM DUAL
         UNION ALL
         SELECT 'ITEM-C' FROM DUAL
         UNION ALL
         SELECT 'ITEM-D' FROM DUAL
         UNION ALL
         SELECT 'ITEM-E' FROM DUAL
         UNION ALL
         SELECT 'ITEM-F' FROM DUAL
         UNION ALL
         SELECT 'ITEM-G' FROM DUAL
         UNION ALL
         SELECT 'ITEM-H' FROM DUAL)
SELECT item, 'GC-' || TO_CHAR (CEIL (ROWNUM / 5), 'FM00') AS groupcode
  FROM items;

推荐阅读