SQL 查询：从过滤、分组到连接和窗口统计

USE engineering_notes;

INSERT INTO projects (name) VALUES ('api-service');
INSERT INTO tasks (project_id, title, status) VALUES
  (1, 'write SQL queries', 'done'),
  (2, 'define routes', 'doing'),
  (2, 'add tests', 'todo'),
  (2, 'write logs', 'done');

SELECT id, title, status
FROM tasks
WHERE status IN ('todo', 'doing')
  AND title LIKE '%API%'
ORDER BY created_at DESC, id DESC;

SELECT DISTINCT status,
       CASE status
         WHEN 'todo' THEN '待处理'
         WHEN 'doing' THEN '进行中'
         ELSE '已完成'
       END AS status_label
FROM tasks
WHERE due_at IS NULL;

SELECT p.id, p.name, COUNT(t.id) AS task_count
FROM projects AS p
LEFT JOIN tasks AS t ON t.project_id = p.id
GROUP BY p.id, p.name
HAVING COUNT(t.id) >= 2
ORDER BY task_count DESC;

WITH task_stats AS (
    SELECT project_id,
           COUNT(*) AS total_count,
           SUM(status = 'done') AS done_count
    FROM tasks
    GROUP BY project_id
)
SELECT p.name, s.done_count, s.total_count,
       ROUND(s.done_count / s.total_count * 100, 1) AS done_percent
FROM task_stats AS s
JOIN projects AS p ON p.id = s.project_id;

SELECT project_id, title, created_at,
       ROW_NUMBER() OVER (
           PARTITION BY project_id
           ORDER BY created_at, id
       ) AS project_task_number
FROM tasks;

SELECT p.id, p.name
FROM projects AS p
WHERE EXISTS (
    SELECT 1
    FROM tasks AS t
    WHERE t.project_id = p.id AND t.status = 'done'
);