在SQL Server中循环从1到60

发布于 2025-02-13 05:57:58 字数 571 浏览 0 评论 0原文

我有一个表T1如下

我需要将数据从T1复制到另一个称为T2的表。 “月”的额外列，T1的每个记录都需要复制至60次T2，一个月值范围从1到60。

T2还有一个名为 “ rel =“ nofollow noreferrer”> “在此处输入图像描述”

我一直在尝试这样的事情，需要动态地采取月份的价值，例如1到60的循环。有人可以帮忙吗？谢谢

INSERT INTO T2
    SELECT PRODUCT, CUSTOMER, 1 as MONTH
    FROM T1

原文

I have a table T1 as below

I need to copy the data from T1 to another table called T2. T2 has an additional column called 'Month' and each record from T1 needs to be copied to T2 60 times, with Month value ranging from 1 to 60.

I have been trying something like this and need the MONTH value to be taken dynamically , like a loop from 1 to 60. Could someone help please? Thank you

INSERT INTO T2
    SELECT PRODUCT, CUSTOMER, 1 as MONTH
    FROM T1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南薇 2025-02-20 05:57:58

我们可以使用交叉加入方法：

WITH months AS (
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6)) v2(n)
)

INSERT INTO T2 (Product, Customer, Month)
SELECT t1.Product, t1.Customer, m.n
FROM table1 t1
CROSS JOIN months m
WHERE m.n BETWEEN 1 AND 60;

We can use a cross join approach:

WITH months AS (
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6)) v2(n)
)

INSERT INTO T2 (Product, Customer, Month)
SELECT t1.Product, t1.Customer, m.n
FROM table1 t1
CROSS JOIN months m
WHERE m.n BETWEEN 1 AND 60;

回复收藏 0 原文

掐死时间 2025-02-20 05:57:58

交叉连接到一个值1到60的值。-感谢@larnu的答案。

回复收藏 0 原文

烟─花易冷 2025-02-20 05:57:58

我在mySQL中使用了存储过程：

    DELIMITER $

CREATE PROCEDURE auto_insert()
    BEGIN
        DECLARE i1 INT DEFAULT 1;
        WHILE i1 <= 60 DO
            INSERT INTO T2 SELECT *, i1 FROM T1;    
            SET i1 = i1 + 1;
        END WHILE;
    END $
DELIMITER ;

CALL auto_insert;

“这是结果集”

I used stored procedures in MySQL:

    DELIMITER $

CREATE PROCEDURE auto_insert()
    BEGIN
        DECLARE i1 INT DEFAULT 1;
        WHILE i1 <= 60 DO
            INSERT INTO T2 SELECT *, i1 FROM T1;    
            SET i1 = i1 + 1;
        END WHILE;
    END $
DELIMITER ;

CALL auto_insert;

Here is the result set