如何比较同一数据库表的两列并找出差异或随机数值？

Hey there! Let's tackle your problem step by step, based on the scenario shown in your example (where you have a table with columns like id, student_id, roll_no, and you want to compare the two ID columns to find mismatches, plus fetch random entries from those results).

First, to get all rows where student_id and roll_no don't match, you'll use a WHERE clause to compare the two columns. Keep in mind that NULL values need special handling—since comparing anything to NULL returns NULL (which isn't treated as "true" in SQL), we'll include checks for NULLs if you want to count those as mismatches too.

Here's a basic SQL query for this:

SELECT *
FROM your_table_name
WHERE student_id != roll_no
   OR student_id IS NULL
   OR roll_no IS NULL;

Notes: #

• If your columns are different data types (e.g., one is numeric, the other is string), use CAST to align them before comparing:

  SELECT *
  FROM your_table_name
  WHERE CAST(student_id AS CHAR) != CAST(roll_no AS CHAR)
     OR student_id IS NULL
     OR roll_no IS NULL;
  

• If you don't want to include NULLs in your mismatch results, just remove the OR student_id IS NULL OR roll_no IS NULL part.

Once you have your mismatch rows, you can add randomization to pull a subset of them. The syntax for random ordering varies by database—here are the most common ones:

MySQL/MariaDB #

Use ORDER BY RAND() with LIMIT to specify how many random rows you want:

SELECT *
FROM your_table_name
WHERE student_id != roll_no
   OR student_id IS NULL
   OR roll_no IS NULL
ORDER BY RAND()
LIMIT 10; -- Replace 10 with your desired number of random rows

PostgreSQL #

Use ORDER BY RANDOM() instead:

SELECT *
FROM your_table_name
WHERE student_id != roll_no
   OR student_id IS NULL
   OR roll_no IS NULL
ORDER BY RANDOM()
LIMIT 10;

SQL Server #

Use ORDER BY NEWID() with TOP:

SELECT TOP 10 * -- Replace 10 with your desired number of random rows
FROM your_table_name
WHERE student_id != roll_no
   OR student_id IS NULL
   OR roll_no IS NULL
ORDER BY NEWID();

Oracle #

Wrap your mismatch query in a subquery and use ORDER BY DBMS_RANDOM.VALUE() with FETCH FIRST:

SELECT *
FROM (
    SELECT *
    FROM your_table_name
    WHERE student_id != roll_no
       OR student_id IS NULL
       OR roll_no IS NULL
)
ORDER BY DBMS_RANDOM.VALUE()
FETCH FIRST 10 ROWS ONLY; -- Replace 10 with your desired number of random rows

Quick Tip #

If you just want random rows from the entire table (not just mismatches), simply remove the WHERE clause from any of the above queries.

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