sqlserver sql语句4、 写一段代码,在表Students_×××中自动产生100条记录,其中女生约占30%。(附代码)
时间: 2024-03-16 11:45:48 浏览: 19
以下是在 SQL Server 数据库中,向表 `Students_XXX` 中自动产生 100 条记录,其中女生约占 30% 的 SQL 查询语句:
```
-- 在表中插入70位女生
INSERT INTO Students_XXX (ID, Name, Gender)
SELECT TOP 70 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'Student' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10)), 'Female'
FROM sys.objects a CROSS JOIN sys.objects b
-- 在表中插入30位男生
INSERT INTO Students_XXX (ID, Name, Gender)
SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'Student' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10)), 'Male'
FROM sys.objects a CROSS JOIN sys.objects b
-- 更新表中的记录,使女生占比约为30%
UPDATE Students_XXX
SET Gender = CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) <= 30 THEN 'Male' ELSE 'Female' END
WHERE 1=1
```
这段代码中,首先向表中插入了 70 位女生和 30 位男生,接着通过