sql - 在现有表中创建序列并复制行

Question

我有一个简短的表格，其中包含:

产品(短文)

Material (短文)

金额(数)

来自(号码)

至(号码)

如何在 MS Access 中创建一个包含序列的新列，该列使用列中的边界值 From和 To ? (我试过 concat ，但它返回短文本)。

最重要的是，我想复制其他列中的所有值，并相应地将它们粘贴到新创建的行中。

值 From和 To这是我的序列边界没有模式。



进入:

Answer 1

您可以在此处尝试日历/序列表方法，在该方法中，您将包含整个序列的表内部连接到当前表:

SELECT
    t2.Product,
    t2.Material,
    t2.Amount,
    t2.[From],
    t2.[To],
    t1.Sequence
FROM
(
    SELECT 1 AS Sequence FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 2 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 3 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 4 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 5 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 6 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 7 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 8 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 9 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
    UNION
    SELECT 10 FROM (SELECT COUNT(*) FROM MSysResources) AS dual
) t1
LEFT JOIN yourTable t2
    ON t1.Sequence BETWEEN t2.[From] AND t2.[To]
ORDER BY
    t1.Sequence;