用户全名显示异常：SQL查询输出格式修正需求

The issue with your current query is that you’re selecting three separate columns and only aliasing the middle name as userName—that’s why only the middle name shows up on the page. To get the desired format (Last Suffix, First Middle), we need to concatenate all relevant name parts into a single column, while handling cases where the suffix might be null to avoid extra spaces or commas.

Here’s the corrected SQL query:

public String getSubjectSql() { 
    return " SELECT " + 
           " CONCAT_WS(' ', u.c_last_name, u.c_suffix) || ', ' || CONCAT_WS(' ', u.c_first_name, u.c_middle_name) AS userName " + 
           " FROM t_user u "; 
}

Key Changes Explained: #

• CONCAT_WS(' ', u.c_last_name, u.c_suffix): This function concatenates the last name and suffix with a space between them, but only includes the space if both values exist. If the suffix is null, it returns just the last name without an extra trailing space.
• || ', ' ||: Adds the comma and space separator between the last/suffix section and the first/middle section.
• CONCAT_WS(' ', u.c_first_name, u.c_middle_name): Similarly, concatenates the first and middle names with a space, gracefully handling null middle names (so you won’t get an extra space if there’s no middle name).

Example with Your Sample User: #

For a user with:

• c_last_name = 'Williams'
• c_suffix = 'VII'
• c_first_name = 'Sam'
• c_middle_name = 'Andrew'

The query will return: Williams VII, Sam Andrew which matches your desired format.

Note on SQL Dialects: #

If your database doesn’t support CONCAT_WS (like some older versions), use a CASE statement to handle null values explicitly:

public String getSubjectSql() { 
    return " SELECT " + 
           " CASE WHEN u.c_suffix IS NOT NULL THEN u.c_last_name || ' ' || u.c_suffix ELSE u.c_last_name END || ', ' || " +
           " CASE WHEN u.c_middle_name IS NOT NULL THEN u.c_first_name || ' ' || u.c_middle_name ELSE u.c_first_name END AS userName " + 
           " FROM t_user u "; 
}

This achieves the same result by checking for nulls before concatenation.

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