如何通过SQL从关联表输出指定格式的控制台内容？

Alright, let's figure out how to get that desired output from your two tables. I'll break this down with concrete code examples for common databases, since syntax can vary a bit.

Step 1: Set Up Test Tables & Data #

First, let's recreate your tables with the sample data so you can test the query directly:

-- Create employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

INSERT INTO employees (id, name)
VALUES (42, 'John'), (43, 'Jane');

-- Create timesheets table with foreign key
CREATE TABLE timesheets (
    id INT PRIMARY KEY,
    employee_id INT NOT NULL REFERENCES employees(id),
    hours DECIMAL(5,2) NOT NULL,
    date DATE NOT NULL
);

INSERT INTO timesheets (id, employee_id, hours, date)
VALUES
    (1, 42, 4.5, '2020-12-01'),
    (2, 42, 7.0, '2020-12-02'),
    (3, 43, 5.5, '2020-12-01'),
    (4, 43, 6.0, '2020-12-02');

Step 2: Query to Generate Desired Output #

The core idea is to:

1. Join the employees and timesheets tables on the foreign key relationship
2. Calculate total hours per employee per day
3. Format the date into a weekday name (e.g., Monday, Tuesday)
4. Aggregate employee-hour strings into a single line per weekday

MySQL Version #

SELECT
    DAYNAME(t.date) AS weekday,
    GROUP_CONCAT(
        CONCAT(e.name, ' (', SUM(t.hours), ' hours)')
        ORDER BY e.name
        SEPARATOR ', '
    ) AS employee_hours
FROM timesheets t
INNER JOIN employees e ON t.employee_id = e.id
GROUP BY t.date, DAYNAME(t.date)
ORDER BY t.date;

PostgreSQL Version #

SELECT
    TO_CHAR(t.date, 'FMDay') AS weekday,
    STRING_AGG(
        CONCAT(e.name, ' (', SUM(t.hours), ' hours)'),
        ', '
        ORDER BY e.name
    ) AS employee_hours
FROM timesheets t
INNER JOIN employees e ON t.employee_id = e.id
GROUP BY t.date, TO_CHAR(t.date, 'FMDay')
ORDER BY t.date;

SQL Server Version #

SELECT
    DATENAME(WEEKDAY, t.date) AS weekday,
    STRING_AGG(
        CONCAT(e.name, ' (', SUM(t.hours), ' hours)'),
        ', '
    ) WITHIN GROUP (ORDER BY e.name) AS employee_hours
FROM timesheets t
INNER JOIN employees e ON t.employee_id = e.id
GROUP BY t.date, DATENAME(WEEKDAY, t.date)
ORDER BY t.date;

Step 3: What the Output Looks Like #

When you run the query, you'll get results formatted like this (note: 2020-12-01 is a Tuesday, 2020-12-02 is a Wednesday):

Key Details to Note #

• Weekday Formatting: Functions like DAYNAME (MySQL), TO_CHAR (PostgreSQL), and DATENAME (SQL Server) handle converting dates to weekday names. Adjust the format string if you need lowercase or different casing.
• String Aggregation: GROUP_CONCAT (MySQL), STRING_AGG (PostgreSQL/SQL Server) are used to combine multiple employee-hour entries into a single comma-separated string.
• Sorting: We added ORDER BY e.name in the aggregation to keep employee names in alphabetical order, matching the clean look of your example.

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