PostgreSQL：如何获取每行中长度最大的列值

Since manually writing a CASE statement for 20+ columns is tedious and error-prone, dynamic SQL is the optimal approach here. It automatically generates the comparison logic for all columns, then executes the resulting query to get your desired output.

Step-by-Step Dynamic SQL Approach (MySQL Example) #

This script will generate and run a query that returns each row along with the value from the column that has the maximum length (treating NULL values as having a length of 0):

SET @tableName = 'your_table';
SET @idColumn = 'ID';

-- Fetch all non-ID columns to include in the SELECT clause
SELECT GROUP_CONCAT(QUOTE(COLUMN_NAME) SEPARATOR ', ') INTO @columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
  AND COLUMN_NAME != @idColumn;

-- Generate the CASE statement logic to compare column lengths
SELECT GROUP_CONCAT(
  CONCAT(
    'WHEN COALESCE(LENGTH(', QUOTE(COLUMN_NAME), '), 0) >= ALL(',
    'SELECT MAX(COALESCE(LENGTH(c), 0)) FROM (VALUES ',
    (SELECT GROUP_CONCAT(CONCAT('(', QUOTE(c.COLUMN_NAME), ')') SEPARATOR ', ')
     FROM INFORMATION_SCHEMA.COLUMNS c
     WHERE TABLE_NAME = @tableName
       AND COLUMN_NAME != @idColumn),
    ') AS temp(c)) THEN ', QUOTE(COLUMN_NAME)
  ) SEPARATOR ' '
) INTO @caseStatement
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
  AND COLUMN_NAME != @idColumn;

-- Build the full query string
SET @sql = CONCAT(
  'SELECT ', @columns, ', ', QUOTE(@idColumn), ', ',
  'CASE ', @caseStatement, ' ELSE NULL END AS Max_Column ',
  'FROM ', @tableName
);

-- Execute the dynamic query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Adaptations for Other Databases #

• SQL Server: Replace LENGTH() with LEN(), GROUP_CONCAT() with STRING_AGG(), and use sp_executesql for execution:

  DECLARE @tableName NVARCHAR(128) = 'your_table';
  DECLARE @idColumn NVARCHAR(128) = 'ID';
  DECLARE @columns NVARCHAR(MAX);
  DECLARE @caseStatement NVARCHAR(MAX);
  DECLARE @sql NVARCHAR(MAX);
  
  SELECT @columns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @tableName AND COLUMN_NAME != @idColumn;
  
  SELECT @caseStatement = STRING_AGG(
    CONCAT(
      'WHEN COALESCE(LEN(', QUOTENAME(COLUMN_NAME), '), 0) >= ALL(',
      'SELECT MAX(COALESCE(LEN(c), 0)) FROM (VALUES ',
      STRING_AGG(CONCAT('(', QUOTENAME(c.COLUMN_NAME), ')'), ', '),
      ') AS temp(c)) THEN ', QUOTENAME(COLUMN_NAME)
    ),
    ' '
  )
  FROM INFORMATION_SCHEMA.COLUMNS c
  WHERE TABLE_NAME = @tableName AND COLUMN_NAME != @idColumn;
  
  SET @sql = CONCAT(
    'SELECT ', @columns, ', ', QUOTENAME(@idColumn), ', ',
    'CASE ', @caseStatement, ' ELSE NULL END AS Max_Column ',
    'FROM ', @tableName
  );
  
  EXEC sp_executesql @sql;
  

• PostgreSQL: Use STRING_AGG() and LENGTH(), wrapped in a PL/pgSQL block:

  DO $$
  DECLARE
    tableName TEXT := 'your_table';
    idColumn TEXT := 'ID';
    columns TEXT;
    caseStatement TEXT;
    sql TEXT;
  BEGIN
    SELECT STRING_AGG(quote_ident(column_name), ', ') INTO columns
    FROM information_schema.columns
    WHERE table_name = tableName AND column_name != idColumn;
  
    SELECT STRING_AGG(
      CONCAT(
        'WHEN COALESCE(LENGTH(', quote_ident(column_name), '), 0) >= ALL(',
        'SELECT MAX(COALESCE(LENGTH(c), 0)) FROM (VALUES ',
        (SELECT STRING_AGG(CONCAT('(', quote_ident(c.column_name), ')'), ', ')
         FROM information_schema.columns c
         WHERE table_name = tableName AND column_name != idColumn),
        ') AS temp(c)) THEN ', quote_ident(column_name)
      ),
      ' '
    ) INTO caseStatement
    FROM information_schema.columns
    WHERE table_name = tableName AND column_name != idColumn;
  
    sql := CONCAT(
      'SELECT ', columns, ', ', quote_ident(idColumn), ', ',
      'CASE ', caseStatement, ' ELSE NULL END AS Max_Column ',
      'FROM ', tableName
    );
  
    EXECUTE sql;
  END $$;
  

How It Works #

1. Fetch Columns: We first retrieve all columns from the table except the ID column (since we don't want to compare the ID value).
2. Generate CASE Logic: For each column, we create a WHEN clause that checks if its length (with NULL treated as 0) is greater than or equal to the maximum length of all other columns in the row. If true, we return that column's value.
3. Execute Dynamic Query: We assemble the full query and run it, which returns your original data plus the Max_Column containing the longest value per row.

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