Mysql中基于列值生成行

a8jjtwal 于 2022-12-17 发布在 Mysql

关注(0)|答案(1)|浏览(164)

我想根据Producttemp表中Column QuantityTable的单元格值插入行，并插入到表ProductionActual中。例如，第一个表如下所示：
表产品温度

+-------------+----------+
| Requisition | Quantity |
+-------------+----------+
| GJ002_01_AD |    5     |
| GJ002_02_AD |    3     |
+-------------+----------+

我希望输出如下：
表产品实际值

+-------------+----------+----------+
| Requisition | Quantity |  Series  |
+-------------+----------+----------+
| GJ002_01_AD |     5    |     1    |
| GJ002_01_AD |     5    |     2    |
| GJ002_01_AD |     5    |     3    |
| GJ002_01_AD |     5    |     4    |
| GJ002_01_AD |     5    |     5    |
| GJ002_02_AD |     3    |     1    |
| GJ002_02_AD |     3    |     2    |
| GJ002_02_AD |     3    |     3    |

我发现类似的问题下面的SQL服务器，但它不工作在MySQL数据库
根据列值生成行

mysql

来源：https://stackoverflow.com/questions/74767679/generating-rows-based-on-column-value-in-mysql

1条答案

按热度按时间

ldioqlga1#

尝试此操作，这将创建具有连续数字的新行
'

WITH RECURSIVE 
Factor (Requisition, Quantity, Series, FactorKey) AS
(
    SELECT Requisition, Quantity,1,CONCAT(Requisition, Quantity,) FROM Producttemp
    UNION ALL
    SELECT 
        Requisition, 
        Quantity,
        IF(FactorKey != CONCAT(Requisition, Quantity),2,Series+1) AS Series ,
        CONCAT(Requisition, Quantity)
    FROM 
        Quantity WHERE Series<Quantity 
    
)
SELECT * FROM Factor ORDER BY Requisition, Quantity;

赞(0）回复(0）举报 2022-12-17

我来回答

Mysql中基于列值生成行

1条答案

相关问题

热门标签

最新问答