如何将多列归为同一别名？TEST表字段分组需求问询

Alright, I get what you're going for here—you want to logically group a set of columns under an alias (like Demographics) without merging them into a single column, similar to Excel's outline grouping, and later join against a similarly grouped set from another table. Let's break down practical ways to achieve this, since plain SQL doesn't have a native "column group alias" feature, but we can simulate it effectively:

1. Prefix Naming (Universal, Simple Solution) #

The most straightforward approach is to add a consistent prefix to your column aliases to signal they belong to the Demographics group. This works across all SQL dialects and makes joins against another table's grouped columns intuitive.

Example query for your TEST table:

SELECT
    First_name AS "Demographics.First_name",
    Last_name AS "Demographics.Last_name",
    Gender AS "Demographics.Gender",
    DOB AS "Demographics.DOB",
    AGE AS "Demographics.Age"
FROM TEST;

When joining against another table (say, CUSTOMERS) with a matching grouped column set, you can reference them clearly:

SELECT
    t."Demographics.First_name",
    c."Demographics.First_name" AS Customer_FirstName
FROM TEST t
JOIN CUSTOMERS c
    ON t."Demographics.First_name" = c."Demographics.First_name"
    AND t."Demographics.Last_name" = c."Demographics.Last_name";

2. JSON/JSONB Encapsulation (PostgreSQL, MySQL 8.0+) #

If you want a formal logical grouping (while still keeping individual columns accessible), you can wrap the columns into a JSON object. This lets you reference the group as a single entity for joins, while retaining separate columns for display.

PostgreSQL example:

SELECT
    First_name,
    Last_name,
    Gender,
    DOB,
    AGE,
    json_build_object(
        'First_name', First_name,
        'Last_name', Last_name,
        'Gender', Gender,
        'DOB', DOB,
        'Age', AGE
    ) AS Demographics
FROM TEST;

To join against another table's Demographics JSON column:

SELECT *
FROM TEST t
JOIN CUSTOMERS c
    ON t.Demographics->>'First_name' = c.Demographics->>'First_name'
    AND t.Demographics->>'Last_name' = c.Demographics->>'Last_name';

3. Client-Side Grouping (Excel-Style Display) #

If your main goal is the visual grouping (like folding columns in Excel), most SQL client tools (DBeaver, DataGrip, SSMS) support this natively without changing your SQL. Just run a standard query selecting the columns in order:

SELECT First_name, Last_name, Gender, DOB, AGE FROM TEST;

Then use your client's grouping feature (e.g., right-click columns in SSMS Results Grid > Group By) to fold these columns under a Demographics header.

Why Your t1/t2 Table Alias Approach Didn't Work #

Table aliases (t1, t2) apply to the entire table, not subsets of columns. So when you use t1.First_name, it's just referencing a column from the aliased table—not grouping it with other columns. The methods above target column-level grouping instead.

内容的提问来源于stack exchange，提问作者idkfa.bfg2