如何查询授课超2门的教职工及编写授课超1门的SQL查询语句？

Let's break down both questions using your provided database schema. First, a quick reminder that we'll be working primarily with the Staff table (for staff details) and Taught_by table (which tracks which staff teach which units).

1. Find Staff Who Teach More Than 2 Units #

To get staff who teach over 2 distinct units, we need to group staff by their ID, count how many unique units they're assigned to, then filter for those with a count greater than 2. We'll also join with the Staff table to pull in their name and other details instead of just an ID.

Here's the query:

SELECT s.Staff_id, s.StaffName, s.Position, s.Gender
FROM Staff s
JOIN Taught_by tb ON s.Staff_id = tb.Staff_id
GROUP BY s.Staff_id, s.StaffName, s.Position, s.Gender
HAVING COUNT(DISTINCT tb.Unit_code) > 2;

Notes: #

• COUNT(DISTINCT tb.Unit_code) ensures we count unique units (in case a staff member teaches the same unit on multiple weekdays, we don't double-count it).
• The GROUP BY includes all non-aggregated columns from the Staff table (this is required in most SQL dialects like PostgreSQL, MySQL with strict mode, etc.).

2. SQL Query to Show Staff Who Teach More Than 1 Unit #

This is almost identical to the first query—we just adjust the count threshold to greater than 1. Again, we'll join with Staff to get full staff details:

SELECT s.Staff_id, s.StaffName, s.Position, s.Gender
FROM Staff s
JOIN Taught_by tb ON s.Staff_id = tb.Staff_id
GROUP BY s.Staff_id, s.StaffName, s.Position, s.Gender
HAVING COUNT(DISTINCT tb.Unit_code) > 1;

Optional: If you only need Staff IDs #

If you don't need the full staff details, you can simplify the query to:

SELECT Staff_id
FROM Taught_by
GROUP BY Staff_id
HAVING COUNT(DISTINCT Unit_code) > 1;

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