sql - 如何将一个选择查询中的值用于联合中的另一个?
问题描述
我正在尝试在“用户”表中插入由随机值组成的行,其中电子邮件取决于用户名的值:
最后一个 SELECT 查询没有看到“用户名”行(不存在)
我怎样才能解决这个问题?
SELECT(
SELECT
string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as username
from (values('abcdefghijklmnopqrstuvwxyz0123456789')) as symbols(characters) join generate_series(1, 15) on 1 = 1
UNION
SELECT
string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as password
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789*')) as symbols(characters) join generate_series(1, 15) on 1 = 1
UNION
SELECT
string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as phonenumber
from (values('0123456789')) as symbols(characters) join generate_series(1, 9) on 1 = 1
UNION
SELECT
(username || '@' || (
CASE (RANDOM() * 2)::INT
WHEN 0 THEN 'gmail'
WHEN 1 THEN 'hotmail'
WHEN 2 THEN 'yahoo'
END
) || '.com') AS email
) INTO "User" from generate_series(1,10000)
这也不起作用:
SELECT(
SELECT
string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as username
from (values('abcdefghijklmnopqrstuvwxyz0123456789')) as symbols(characters) join generate_series(1, 15) on 1 = 1
UNION
SELECT
string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as password
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')) as symbols(characters) join generate_series(1, 15) on 1 = 1
as select1
UNION
SELECT
string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as phonenumber
from (values('0123456789')) as symbols(characters) join generate_series(1, 9) on 1 = 1
UNION
SELECT
(select1.username || '@' || (
CASE (RANDOM() * 2)::INT
WHEN 0 THEN 'gmail'
WHEN 1 THEN 'hotmail'
WHEN 2 THEN 'yahoo'
END
) || '.com') AS email
) INTO "User" from generate_series(1,10000)
解决方案
考虑递归 CTE 查询并删除顶级列的子查询:
WITH RECURSIVE main AS
(
SELECT
string_agg(substr(shortchars, (random() * length(shortchars) + 1)::integer, 1),'') AS "username"
, string_agg(substr(longchars, (random() * length(longchars) + 1)::integer, 1), '') AS "password"
, substr(string_agg(substr(nums, (random() * length(nums) + 1)::integer, 1), ''),1,9) AS "phonenumber"
, 1 AS n
FROM (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*')) as longchars(longchars)
CROSS JOIN (values('abcdefghijklmnopqrstuvwxyz0123456789')) as shortchars(shortchars)
CROSS JOIN (values('0123456789')) as num(nums)
CROSS JOIN generate_series(1, 15)
UNION ALL
SELECT
string_agg(substr(shortchars, (random() * length(shortchars) + 1)::integer, 1),'') AS "username"
, string_agg(substr(longchars, (random() * length(longchars) + 1)::integer, 1), '') AS "password"
, substr(string_agg(substr(nums, (random() * length(nums) + 1)::integer, 1), ''),1,9) AS "phonenumber"
, n + 1
FROM (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*')) as longchars(longchars)
CROSS JOIN (values('abcdefghijklmnopqrstuvwxyz0123456789')) as shortchars(shortchars)
CROSS JOIN (values('0123456789')) as num(nums)
CROSS JOIN generate_series(1, 15)
CROSS JOIN (SELECT n FROM main LIMIT 1) AS mn
WHERE n < 10
GROUP BY mn.n
)
SELECT CONCAT(main."username",
'@',
( CASE (RANDOM() * 2)::INT
WHEN 0 THEN 'gmail'
WHEN 1 THEN 'hotmail'
WHEN 2 THEN 'yahoo'
END ),
'com') AS email
, main."username"
, main."password"
, main."phonenumber"
FROM main
Online Demo
推荐阅读
- java - 无法将 java.util.HashMap 类型的值转换为字符串。将字符串转换为映射
- json - Scala 对象、数据集和数据框之间的链接
- win2d - 使用 Win2D 是否可以在没有绘图会话的情况下获得文本大小?
- ios - 运行 ionic cordova build ios --prod 时出错
- python - 选择一个随机图像并为水印做 PIL
- python - 使用 Stripe 与 Angular 和 Django 创建新用户时,如何添加其他公司字段?
- c++ - C++/C:将字符串转换为初始化列表
- python - Python readlines() 返回空字符串
- python - i += x > 0 是什么意思?
- electron - 是否有相当于 ipcRender.invoke 的主进程?