随机值不会插入SQLite表中

wqnecbli 于 7个月前发布在 SQLite

关注(0)|答案(2)|浏览(74)

我在SQLite中创建了一个数据库，一个Table. Schema是这样的：

CREATE TABLE Students (
    StudentID INTEGER PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Gender TEXT CHECK (Gender IN ('Male', 'Female', 'Other')),
    Age INTEGER,
    RegistrationDate DATE
);

字符串
我尝试运行一些查询来手动插入1000行数据，但年龄和数据给出了奇怪的值。这是查询：

WITH RECURSIVE
  cnt(x) AS (
     SELECT 1
     UNION ALL
     SELECT x+1 FROM cnt
     WHERE x<1000
  )
INSERT INTO Students (FirstName, LastName, Gender, Age, RegistrationDate)
SELECT
    'FirstName' || x,
    'LastName' || x,
    CASE WHEN x % 3 = 0 THEN 'Male' WHEN x % 3 = 1 THEN 'Female' ELSE 'Other' END,
    CAST(18 + ROUND(RANDOM() * 10) AS INTEGER), -- Age between 18 and 28
    DATE('now', '-' || ROUND(RANDOM() * 365, 0) || ' days') -- Random registration date in the last year
FROM cnt;

型
这是当我从Students中选择 * 时的结果;

1|FirstName1|LastName1|Female|-9223372036854775808|
2|FirstName2|LastName2|Other|9223372036854775807|
3|FirstName3|LastName3|Male|-9223372036854775808|
4|FirstName4|LastName4|Female|-9223372036854775808|
5|FirstName5|LastName5|Other|9223372036854775807|
6|FirstName6|LastName6|Male|-9223372036854775808|
7|FirstName7|LastName7|Female|-9223372036854775808|
8|FirstName8|LastName8|Other|-9223372036854775808|

型

sqlite

来源：https://stackoverflow.com/questions/77451704/random-values-are-not-getting-inserted-in-sqlite-table

2条答案

按热度按时间

oo7oh9g91#

阅读文档：
函数的作用是：返回一个介于-9223372036854775808和+9223372036854775807之间的伪随机整数。
所以，解决方案是使用modulo：

ABS(RANDOM()) % 11 + 18, -- Age between 18 and 28

字符串
日期也是如此：

DATE('now', '-' || (ABS(RANDOM()) % 366) || ' days') -- Random registration date in the last year

型

赞(0）回复(0）举报 7个月前

c6ubokkw2#

我使用模数（%）更新了逻辑。下面是示例代码：

WITH RECURSIVE
  cnt(x) AS (
     SELECT 1
     UNION ALL
     SELECT x+1 FROM cnt
     WHERE x<1000
  )
INSERT INTO Students (FirstName, LastName, Gender, Age, RegistrationDate)
SELECT
    'FirstName' || x,
    'LastName' || x,
    CASE WHEN x % 3 = 0 THEN 'Male' WHEN x % 3 = 1 THEN 'Female' ELSE 'Other' END,
    18 + ABS(CAST(RANDOM() % 11 AS INTEGER)), -- Age between 18 and 28
    DATE('now', '-' || (ABS(RANDOM()) % 365) || ' days') -- Random registration date in the last year
FROM cnt;

字符串
以下是示例输出：

的数据
这里是dbfiddle

赞(0）回复(0）举报 7个月前

我来回答

随机值不会插入SQLite表中

2条答案

相关问题

热门标签

最新问答