如何创建含PrimaryNumber与ThousandRange列的SQL数字引用表并填充数据

嘿，作为SQL新手，这个需求其实很好实现，我分步骤给你讲清楚怎么创建并填充这个表，不同数据库的写法略有差异，我都给你列出来～

1. 先创建基础表 #

首先我们得先建出包含PrimaryNumber和ThousandRange的表，SQL语句如下：

CREATE TABLE NumberReference (
    PrimaryNumber INT PRIMARY KEY,
    ThousandRange VARCHAR(11) -- 足够容纳类似"20000-20999"这样的字符串
);

这里把PrimaryNumber设为主键，确保每个数字唯一，ThousandRange用字符串类型来存储范围文本。

2. 填充数据并计算千位范围 #

接下来要把1到20000的整数填进去，同时自动计算对应的ThousandRange。下面是主流数据库的实现方式：

对于SQL Server / Azure SQL #

可以用递归CTE来生成序列并计算范围：

WITH NumberSequence AS (
    SELECT 1 AS PrimaryNumber
    UNION ALL
    SELECT PrimaryNumber + 1
    FROM NumberSequence
    WHERE PrimaryNumber < 20000
)
INSERT INTO NumberReference (PrimaryNumber, ThousandRange)
SELECT 
    PrimaryNumber,
    CONCAT(FLOOR(PrimaryNumber / 1000) * 1000, '-', FLOOR(PrimaryNumber / 1000) * 1000 + 999) AS ThousandRange
FROM NumberSequence
OPTION (MAXRECURSION 0); -- 因为递归超过100次，需要开这个选项

对于MySQL 8.0+ #

同样支持递归CTE：

WITH RECURSIVE NumberSequence AS (
    SELECT 1 AS PrimaryNumber
    UNION ALL
    SELECT PrimaryNumber + 1
    FROM NumberSequence
    WHERE PrimaryNumber < 20000
)
INSERT INTO NumberReference (PrimaryNumber, ThousandRange)
SELECT 
    PrimaryNumber,
    CONCAT(FLOOR(PrimaryNumber / 1000) * 1000, '-', FLOOR(PrimaryNumber / 1000) * 1000 + 999) AS ThousandRange
FROM NumberSequence;

如果是MySQL 5.x版本，递归CTE不支持，可以用存储过程循环插入：

DELIMITER //
CREATE PROCEDURE FillNumberReference()
BEGIN
    DECLARE num INT DEFAULT 1;
    WHILE num <= 20000 DO
        INSERT INTO NumberReference (PrimaryNumber, ThousandRange)
        VALUES (
            num,
            CONCAT(FLOOR(num / 1000) * 1000, '-', FLOOR(num / 1000) * 1000 + 999)
        );
        SET num = num + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程
CALL FillNumberReference();

对于PostgreSQL #

PostgreSQL有更简便的generate_series函数：

INSERT INTO NumberReference (PrimaryNumber, ThousandRange)
SELECT 
    num AS PrimaryNumber,
    CONCAT(FLOOR(num / 1000) * 1000, '-', FLOOR(num / 1000) * 1000 + 999) AS ThousandRange
FROM generate_series(1, 20000) AS num;

逻辑说明 #

• FLOOR(PrimaryNumber / 1000) * 1000：这个表达式会把数字向下取整到最近的千位数，比如1234会得到1000，567得到0，20000得到20000。
• 然后拼接上-和起始值+999，就得到了对应的千位范围字符串，完全符合你给出的例子（比如1000-1999、2000-2999）。

如果需要调整1-999的范围显示（比如改成"1-999"而不是"0-999"），可以加个CASE判断：

CASE 
    WHEN PrimaryNumber < 1000 THEN '1-999'
    ELSE CONCAT(FLOOR(PrimaryNumber / 1000) * 1000, '-', FLOOR(PrimaryNumber / 1000) * 1000 + 999)
END AS ThousandRange

内容的提问来源于stack exchange，提问作者scedsh8919