SQL从两列随机取值：第三方测试数据姓名与D.O.B脱敏问询

Hey there! Let's work through this problem together. You need to create anonymized test data by mixing random first names and surnames from your 10-customer list, plus implement the SQL logic to pick those random values. Here's a straightforward breakdown of how to do this:

Core Idea #

Since you have a list of 10 customer names, we can split first names and surnames into separate pools, then randomly pull from each pool to create new, unlinked name pairs. This keeps real customer name combinations private while giving you valid, varied test data.

SQL Solutions by Database #

Below are tailored queries for major databases to randomly select and mix first names and surnames:

For MySQL/MariaDB #

-- Generate 1 random name pair
SELECT 
    (SELECT firstname FROM customer_names ORDER BY RAND() LIMIT 1) AS random_firstname,
    (SELECT surname FROM customer_names ORDER BY RAND() LIMIT 1) AS random_surname;

-- Generate 10 unique random pairs (adjust the number in the CTE as needed)
WITH number_range AS (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
)
SELECT 
    (SELECT firstname FROM customer_names ORDER BY RAND() LIMIT 1) AS random_firstname,
    (SELECT surname FROM customer_names ORDER BY RAND() LIMIT 1) AS random_surname
FROM number_range;

For PostgreSQL #

-- Generate 1 random name pair
SELECT
    (SELECT firstname FROM customer_names ORDER BY RANDOM() LIMIT 1) AS random_firstname,
    (SELECT surname FROM customer_names ORDER BY RANDOM() LIMIT 1) AS random_surname;

-- Generate 10 random pairs (change the generate_series range for more/less)
SELECT
    (SELECT firstname FROM customer_names ORDER BY RANDOM() LIMIT 1) AS random_firstname,
    (SELECT surname FROM customer_names ORDER BY RANDOM() LIMIT 1) AS random_surname
FROM generate_series(1, 10);

For SQL Server #

-- Generate 1 random name pair
SELECT
    (SELECT TOP 1 firstname FROM customer_names ORDER BY NEWID()) AS random_firstname,
    (SELECT TOP 1 surname FROM customer_names ORDER BY NEWID()) AS random_surname;

-- Generate 10 random pairs
WITH number_range AS (
    SELECT n FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS t(n)
)
SELECT
    (SELECT TOP 1 firstname FROM customer_names ORDER BY NEWID()) AS random_firstname,
    (SELECT TOP 1 surname FROM customer_names ORDER BY NEWID()) AS random_surname
FROM number_range;

Optional: Avoid Duplicate Name Pairs #

If you want to ensure no repeated name combinations, generate all possible unique pairs first, then randomly select from them:

-- MySQL example: Get 10 unique non-original name pairs
SELECT 
    c1.firstname, c2.surname
FROM customer_names c1
CROSS JOIN customer_names c2
WHERE c1.firstname <> c2.firstname -- Optional: Skip pairs that match original customer names
ORDER BY RAND()
LIMIT 10;

Combine with D.O.B. Anonymization #

You can merge this name logic with your existing D.O.B. adjustment in one query. For example (MySQL):

-- Generate full anonymized test data with adjusted D.O.B.
SELECT
    (SELECT firstname FROM customer_names ORDER BY RAND() LIMIT 1) AS test_firstname,
    (SELECT surname FROM customer_names ORDER BY RAND() LIMIT 1) AS test_surname,
    DATE_ADD(original_dob, INTERVAL FLOOR(RAND() * 10) + 1 YEAR) AS test_dob -- Randomly add 1-10 years
FROM customer_data; -- Replace with your table containing original D.O.B. values

This gives you a complete set of anonymized test data ready for third-party use!

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