技术求助：如何使用SQL查找4列重复值及下图提及的最后一列

Hey there! Let's break down your two SQL questions step by step—these are common scenarios I’ve helped troubleshoot a bunch of times.

I’ll cover two common interpretations of this question, since "duplicates across columns" can mean a couple different things:

Scenario 1: Rows Where Any of the 4 Columns Have Matching Values #

If you want to find rows where at least two of your four columns (say col1, col2, col3, col4) have the same value, you can use straightforward comparison logic. Here’s how to do it in most databases:

SELECT *
FROM your_table
WHERE col1 = col2 
   OR col1 = col3 
   OR col1 = col4 
   OR col2 = col3 
   OR col2 = col4 
   OR col3 = col4;

For PostgreSQL users, a more concise approach using arrays works too:

SELECT *
FROM your_table
-- Check if the array of columns has fewer unique values than total columns
WHERE array_length(ARRAY[col1, col2, col3, col4], 1) 
    != array_length(ARRAY(SELECT DISTINCT unnest(ARRAY[col1, col2, col3, col4])), 1);

Scenario 2: Values That Repeat Anywhere Across the 4 Columns (Across All Rows) #

If you want to find values that show up multiple times across all four columns (regardless of which row or column they’re in), you’ll first "unpivot" the columns into a single list, then count occurrences:

SELECT value, COUNT(*) AS occurrence_count
FROM (
    -- Stack all four columns into one column
    SELECT col1 AS value FROM your_table
    UNION ALL
    SELECT col2 AS value FROM your_table
    UNION ALL
    SELECT col3 AS value FROM your_table
    UNION ALL
    SELECT col4 AS value FROM your_table
) AS unpivoted_data
GROUP BY value
-- Filter for values that appear more than once
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC;

If you’re using SQL Server (which supports UNPIVOT), you can write this more cleanly:

SELECT value, COUNT(*) AS occurrence_count
FROM your_table
UNPIVOT (
    value FOR column_names IN (col1, col2, col3, col4)
) AS unpivoted
GROUP BY value
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC;

Since I can’t see the image you’re referencing, I’ll walk through the most common "last column" scenarios people ask about. Pick the one that matches your use case, or share more details if none fit!

Scenario A: The Last Column Is a Calculated/Summary Column #

If the last column is derived from the other columns (like a sum, average, or concatenation), you can compute it directly in your SELECT statement:

SELECT 
    col1, col2, col3, col4,
    -- Example: Sum of the first four columns as the last column
    col1 + col2 + col3 + col4 AS total_sum,
    -- Or another example: Concatenate columns
    CONCAT(col1, '-', col2, '-', col3, '-', col4) AS combined_value
FROM your_table;

Scenario B: The Last Column Is the Latest Record in a Group #

If you need the last value from a grouped dataset (e.g., the most recent order amount per customer), use window functions like ROW_NUMBER():

-- Works in MySQL 8+, PostgreSQL, SQL Server, etc.
SELECT customer_id, last_order_amount
FROM (
    SELECT 
        customer_id, 
        order_amount AS last_order_amount,
        -- Rank orders by date (newest first) per customer
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_rank
    FROM orders
) AS ranked_orders
WHERE row_rank = 1; -- Grab only the latest order per customer

Scenario C: The Last Column Is the Final Row in the Entire Table #

To get the value from the last row (by some sort order, like insertion date or ID), use LIMIT or FETCH FIRST:

-- MySQL
SELECT last_column_name FROM your_table ORDER BY id DESC LIMIT 1;

-- PostgreSQL/SQL Server
SELECT last_column_name FROM your_table ORDER BY id DESC FETCH FIRST 1 ROW ONLY;

If your scenario is different—like the last column is from a joined table, or has a specific filtering rule—just share more details about the table structure and what the last column represents, and I can refine the solution!

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