如何用单条SQL查询获取子表最后完成任务ID并统计记录数？

Hey there! You can absolutely pull all three of those metrics in a single SQL query—no need to run multiple separate calls. Let's break this down with examples that fit your desired output (total: 11, completed: 3, last_completed_task_id: 27):

The core idea is to use aggregate functions alongside conditional logic to calculate all three values in one pass over your subtable. I’ll cover a standard SQL version first, plus dialect-specific shortcuts if you’re using PostgreSQL.

Standard SQL (Works for MySQL, SQL Server, etc.) #

Assuming your subtable is named tasks, with:

• task_id: Unique identifier for each task
• is_completed: A boolean/1-0 flag indicating if the task is done (adjust the condition if you use a string status like 'completed')

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN is_completed = 1 THEN 1 ELSE 0 END) AS completed,
  MAX(CASE WHEN is_completed = 1 THEN task_id ELSE NULL END) AS last_completed_task_id
FROM tasks;

How this works: #

• COUNT(*): Counts every record in the table to get your total of 11.
• SUM(CASE...): Adds up 1 for each completed task, giving you the count of 3 finished items.
• MAX(CASE...): Grabs the highest task_id from completed tasks—this works if your task IDs are auto-incremented and match completion order. If you have a completed_at timestamp (more reliable for tracking when tasks finish), use this adjusted version instead:

SELECT
  summary.total,
  summary.completed,
  latest_task.task_id AS last_completed_task_id
FROM (
  -- First calculate total and completed counts
  SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN is_completed = 1 THEN 1 ELSE 0 END) AS completed
  FROM tasks
) AS summary
-- Get the most recently completed task ID
CROSS JOIN (
  SELECT task_id
  FROM tasks
  WHERE is_completed = 1
  ORDER BY completed_at DESC
  LIMIT 1
) AS latest_task;

PostgreSQL Shortcut #

PostgreSQL supports the FILTER clause, which cleans up the conditional aggregation:

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE is_completed = 1) AS completed,
  MAX(task_id) FILTER (WHERE is_completed = 1) AS last_completed_task_id
FROM tasks;

Key Notes: #

• Replace is_completed, task_id, and completed_at with your actual column names.
• If your completion status uses a string (e.g., status = 'done'), update the condition in the CASE or FILTER clause to match.
• Using a completed_at timestamp ensures you get the truly last completed task, even if task IDs are out of order with completion times.

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