我正在使用 SQL 將示例測驗資料放在一起。
有沒有一種方法可以在每次重新運行相同的 SQL 陳述句時得到不同的結果。
我熟悉使用種子，但我認為它只適用于 PLSQL，如果可能的話我想避免。
是否可以調整 SQL 以模擬種子并為每次新運行提供不同的結果？
CREATE TABLE students (
student_id number(*,0),
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
active VARCHAR2(1) DEFAULT 'Y',
constraint student_pk primary key (student_id));
insert into students (student_id, first_name, last_name, active)
select
level,
CASE round(dbms_random.value(1,25))
WHEN 1 THEN 'Faith'
WHEN 2 THEN 'Tom'
WHEN 3 THEN 'Anna'
WHEN 4 THEN 'Lisa'
WHEN 5 THEN 'Andy'
WHEN 6 THEN 'Thomas'
WHEN 7 THEN 'Alan'
WHEN 8 THEN 'Keith'
WHEN 9 THEN 'Cheryl'
WHEN 10 THEN 'Chester'
WHEN 11 THEN 'Steve'
WHEN 12 THEN 'Mel'
WHEN 13 THEN 'Micheal'
WHEN 14 THEN 'Ron'
WHEN 15 THEN 'Donald'
WHEN 16 THEN 'Carolyn'
WHEN 17 THEN 'Racheal'
WHEN 18 THEN 'Debbie'
WHEN 19 THEN 'Madison'
WHEN 20 THEN 'Danny'
WHEN 21 THEN 'Claude'
WHEN 22 THEN 'Peter'
WHEN 23 THEN 'Edna'
WHEN 24 THEN 'Anita'
WHEN 25 THEN 'Mindy'
END AS first_name,
CASE round(dbms_random.value(1,25))
WHEN 1 THEN 'Andrews'
WHEN 2 THEN 'Thorton'
WHEN 3 THEN 'Smith'
WHEN 4 THEN 'Jones'
WHEN 5 THEN 'Beirs'
WHEN 6 THEN 'Stevens'
WHEN 7 THEN 'Feldman'
WHEN 8 THEN 'Stein'
WHEN 9 THEN 'Ross'
WHEN 10 THEN 'Mednick'
WHEN 11 THEN 'Saltzman'
WHEN 12 THEN 'Kramer'
WHEN 13 THEN 'Monroe'
WHEN 14 THEN 'Hanks'
WHEN 15 THEN 'Dunn'
WHEN 16 THEN 'Dunbar'
WHEN 17 THEN 'Rucker'
WHEN 18 THEN 'Silverberg'
WHEN 19 THEN 'Daniels'
WHEN 20 THEN 'Kern'
WHEN 21 THEN 'Saladino'
WHEN 22 THEN 'Rice'
WHEN 23 THEN 'Sanford'
WHEN 24 THEN 'Krantz'
WHEN 25 THEN 'Roth'
END AS last_name,
CASE MOD(LEVEL, 10)
WHEN 0
THEN 'N'
ELSE 'Y'
end
from dual
connect by level <= 25;
uj5u.com熱心網友回復：
此處提供了解決方案
想法是隨機化順序而不是實際值。生成一系列不同的數字，然后將其順序隨機化。
使用相同的方法可以將其包含到insert.
例子 -
insert into students (student_id, first_name)
WITH cte AS ( SELECT level lvl FROM dual CONNECT BY level <= 7 )
SELECT rownum, case t1.lvl
WHEN 1 THEN 'Faith'
WHEN 2 THEN 'Tom'
WHEN 3 THEN 'Anna'
WHEN 4 THEN 'Lisa'
WHEN 5 THEN 'Andy'
WHEN 6 THEN 'Thomas'
WHEN 7 THEN 'Alan'
end first_name
FROM
(SELECT lvl FROM cte
ORDER BY dbms_random.value() ) t1;
在這里參考小提琴。
uj5u.com熱心網友回復：
您不需要使用CASE運算式。如果您每次都想要一個唯一的名稱，那么您需要一個包含名字串列的表和一個包含姓氏串列的表，然后CROSS JOIN將它們隨機排序并插入它們：
insert into students (student_id, first_name, last_name, active)
WITH first_names (first_name) AS (
SELECT COLUMN_VALUE
FROM TABLE(
SYS.ODCIVARCHAR2LIST(
'Faith', 'Tom', 'Anna', 'Lisa', 'Andy', 'Thomas' -- ...
)
)
),
last_names (last_name) AS (
SELECT COLUMN_VALUE
FROM TABLE(
SYS.ODCIVARCHAR2LIST(
'Andrews', 'Thorton', 'Smith', 'Jones', 'Biers', 'Stevens', 'Feldman' -- ...
)
)
),
random (first_name, last_name) AS (
SELECT first_name, last_name
FROM first_names CROSS JOIN last_names
ORDER BY DBMS_RANDOM.VALUE()
)
SELECT ROWNUM,
first_name,
last_name,
CASE MOD(ROWNUM, 10) WHEN 0 THEN 'N' ELSE 'Y' END
FROM random
WHERE ROWNUM <= 20 -- Get the first 20 rows
;
db<>在這里擺弄
uj5u.com熱心網友回復：
CREATE TABLE students (
student_id number(*,0),
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
active VARCHAR2(1) DEFAULT 'Y',
constraint student_pk primary key (student_id));
insert into students (student_id, first_name, last_name, active)
with fn as (select rownum r, first_name
from (
select case rownum
WHEN 1 THEN 'Faith'
WHEN 2 THEN 'Tom'
WHEN 3 THEN 'Anna'
WHEN 4 THEN 'Lisa'
WHEN 5 THEN 'Andy'
WHEN 6 THEN 'Thomas'
WHEN 7 THEN 'Alan'
WHEN 8 THEN 'Keith'
WHEN 9 THEN 'Cheryl'
WHEN 10 THEN 'Chester'
WHEN 11 THEN 'Steve'
WHEN 12 THEN 'Mel'
WHEN 13 THEN 'Micheal'
WHEN 14 THEN 'Ron'
WHEN 15 THEN 'Donald'
WHEN 16 THEN 'Carolyn'
WHEN 17 THEN 'Racheal'
WHEN 18 THEN 'Debbie'
WHEN 19 THEN 'Madison'
WHEN 20 THEN 'Danny'
WHEN 21 THEN 'Claude'
WHEN 22 THEN 'Peter'
WHEN 23 THEN 'Edna'
WHEN 24 THEN 'Anita'
WHEN 25 THEN 'Mindy'
end as first_name
from (select level l from dual connect by level <= 25)
order by dbms_random.value()
)
)
,ln as (select rownum r, last_name
from (
select case rownum
WHEN 1 THEN 'Andrews'
WHEN 2 THEN 'Thorton'
WHEN 3 THEN 'Smith'
WHEN 4 THEN 'Jones'
WHEN 5 THEN 'Beirs'
WHEN 6 THEN 'Stevens'
WHEN 7 THEN 'Feldman'
WHEN 8 THEN 'Stein'
WHEN 9 THEN 'Ross'
WHEN 10 THEN 'Mednick'
WHEN 11 THEN 'Saltzman'
WHEN 12 THEN 'Kramer'
WHEN 13 THEN 'Monroe'
WHEN 14 THEN 'Hanks'
WHEN 15 THEN 'Dunn'
WHEN 16 THEN 'Dunbar'
WHEN 17 THEN 'Rucker'
WHEN 18 THEN 'Silverberg'
WHEN 19 THEN 'Daniels'
WHEN 20 THEN 'Kern'
WHEN 21 THEN 'Saladino'
WHEN 22 THEN 'Rice'
WHEN 23 THEN 'Sanford'
WHEN 24 THEN 'Krantz'
WHEN 25 THEN 'Roth'
end as last_name
from (select level l from dual connect by level <= 25)
order by dbms_random.value()
)
)
,ac as (select rownum r, active
from (
select case mod(rownum,10) when 0 then 'N' else 'Y' end as active
from (select level l from dual connect by level <= 25)
order by dbms_random.value()
)
)
select fn.r, fn.first_name, ln.last_name, ac.active
from fn
join ln on (fn.r = ln.r)
join ac on (ac.r = ln.r)
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/494497.html