PostgreSQL中多行爱好合并为单行记录的建表、插入与查询方法

Let's break this down into straightforward steps to get exactly the output you need. We'll cover table creation, data insertion, and the query to combine hobbies into a single row per employee.

1. Create the Table(s) #

We'll cover two common storage scenarios—pick the one that matches your existing setup (or use the normalized approach for better database design):

Scenario 1: Single Non-Normalized Table #

If your data is stored in one table where each row represents an employee-hobby pair (with repeated employee details):

CREATE TABLE employee_details (
    emp_name VARCHAR(50),
    hobby VARCHAR(50),
    age INT,
    dob DATE
);

Scenario 2: Normalized Two Tables (Recommended) #

This setup avoids repeating employee data like age/DOB, which is better for maintainability:

-- Stores unique employee information
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50),
    age INT,
    dob DATE
);

-- Links employees to their hobbies (no duplicate data)
CREATE TABLE employee_hobbies (
    emp_id INT REFERENCES employees(emp_id),
    hobby VARCHAR(50),
    PRIMARY KEY (emp_id, hobby) -- Prevents duplicate hobbies for the same employee
);

2. Insert Sample Data #

For Scenario 1 #

INSERT INTO employee_details (emp_name, hobby, age, dob)
VALUES
('LOPEZ', 'Football', 19, '1999-05-11'),
('LOPEZ', 'Swimming', 19, '1999-05-11'),
('LOPEZ', 'Fishing', 19, '1999-05-11');

For Scenario 2 #

-- First insert the core employee record
INSERT INTO employees (emp_name, age, dob)
VALUES ('LOPEZ', 19, '1999-05-11');

-- Then link each hobby to the employee
INSERT INTO employee_hobbies (emp_id, hobby)
VALUES
((SELECT emp_id FROM employees WHERE emp_name = 'LOPEZ'), 'Football'),
((SELECT emp_id FROM employees WHERE emp_name = 'LOPEZ'), 'Swimming'),
((SELECT emp_id FROM employees WHERE emp_name = 'LOPEZ'), 'Fishing');

3. Query to Combine Hobbies into a Single Row #

PostgreSQL's STRING_AGG function is made for this task—it takes grouped string values and concatenates them with your chosen delimiter.

For Scenario 1 #

SELECT
    emp_name,
    STRING_AGG(hobby, ', ') AS hobbies,
    age,
    dob
FROM employee_details
GROUP BY emp_name, age, dob; -- Group by all non-aggregated columns to avoid errors

For Scenario 2 #

SELECT
    e.emp_name,
    STRING_AGG(eh.hobby, ', ') AS hobbies,
    e.age,
    e.dob
FROM employees e
JOIN employee_hobbies eh ON e.emp_id = eh.emp_id
GROUP BY e.emp_id, e.emp_name, e.age, e.dob;

Both queries will return your desired output:

emp_name | hobbies                  | age | dob
---------|--------------------------|-----|------------
LOPEZ    | Football, Swimming, Fishing | 19  | 1999-05-11

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