94 lines
3.9 KiB
SQL
94 lines
3.9 KiB
SQL
-- 周报元数据表（用于快速查询和统计）
|
|
-- 2026-08-27 创建
|
|
|
|
CREATE TABLE IF NOT EXISTS `weekly_reports` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`year` INT NOT NULL COMMENT '年份',
|
|
`week_number` INT NOT NULL COMMENT '周数 (1-53)',
|
|
`start_date` DATE NOT NULL COMMENT '周一日期',
|
|
`end_date` DATE NOT NULL COMMENT '周五日期',
|
|
`project` VARCHAR(50) NOT NULL COMMENT '项目名称 (如 G5/G6)',
|
|
`main_task` VARCHAR(500) COMMENT '本周主要任务',
|
|
`file_path` VARCHAR(255) NOT NULL COMMENT '归档文件路径',
|
|
`has_problems` TINYINT(1) DEFAULT 0 COMMENT '是否存在问题',
|
|
`word_count` INT DEFAULT 0 COMMENT '字数统计',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_year_week` (`year`, `week_number`, `project`),
|
|
KEY `idx_date` (`start_date`, `end_date`),
|
|
KEY `idx_project` (`project`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='周报元数据表';
|
|
|
|
-- 周报每日工作明细表（可选，用于更细粒度的查询）
|
|
CREATE TABLE IF NOT EXISTS `weekly_report_daily` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`report_id` INT NOT NULL COMMENT '周报 ID',
|
|
`day_of_week` TINYINT NOT NULL COMMENT '星期几 (1-5)',
|
|
`work_date` DATE NOT NULL COMMENT '具体日期',
|
|
`work_items` TEXT COMMENT '工作内容列表 (JSON 数组或换行分隔)',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_report` (`report_id`),
|
|
KEY `idx_date` (`work_date`),
|
|
CONSTRAINT `fk_report` FOREIGN KEY (`report_id`) REFERENCES `weekly_reports` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='周报每日工作明细表';
|
|
|
|
-- 周报问题记录表（可选，用于问题追踪）
|
|
CREATE TABLE IF NOT EXISTS `weekly_report_problems` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`report_id` INT NOT NULL COMMENT '周报 ID',
|
|
`problem_description` TEXT NOT NULL COMMENT '问题描述',
|
|
`status` VARCHAR(20) DEFAULT 'open' COMMENT '状态：open/resolved/closed',
|
|
`resolved_at` DATETIME NULL COMMENT '解决时间',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_report` (`report_id`),
|
|
CONSTRAINT `fk_problem_report` FOREIGN KEY (`report_id`) REFERENCES `weekly_reports` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='周报问题记录表';
|
|
|
|
-- 示例数据
|
|
INSERT INTO `weekly_reports` (`year`, `week_number`, `start_date`, `end_date`, `project`, `main_task`, `file_path`, `has_problems`, `word_count`)
|
|
VALUES
|
|
(2026, 29, '2026-07-14', '2026-07-18', 'G5', '库存预警模块开发', 'weekly-reports/2026/2026-W29.md', 0, 500),
|
|
(2026, 31, '2026-07-28', '2026-08-01', 'G5', '委外发料需求开发', 'weekly-reports/2026/2026-W31-周报.md', 0, 600);
|
|
|
|
-- 常用查询示例
|
|
|
|
-- 1. 查询某年所有周报
|
|
SELECT * FROM weekly_reports WHERE year = 2026 ORDER BY week_number;
|
|
|
|
-- 2. 查询某月所有周报
|
|
SELECT * FROM weekly_reports
|
|
WHERE start_date >= '2026-08-01' AND end_date <= '2026-08-31'
|
|
ORDER BY start_date;
|
|
|
|
-- 3. 统计某项目全年周报数量
|
|
SELECT project, COUNT(*) as report_count
|
|
FROM weekly_reports
|
|
WHERE year = 2026
|
|
GROUP BY project;
|
|
|
|
-- 4. 查询存在问题的周报
|
|
SELECT * FROM weekly_reports WHERE has_problems = 1;
|
|
|
|
-- 5. 查询某时间范围的工作天数
|
|
SELECT SUM(day_of_week) as total_days
|
|
FROM weekly_report_daily
|
|
WHERE work_date BETWEEN '2026-07-01' AND '2026-09-30';
|
|
|
|
-- 6. 月度统计（用于考核）
|
|
SELECT
|
|
YEAR(start_date) as year,
|
|
MONTH(start_date) as month,
|
|
project,
|
|
COUNT(*) as weeks_count,
|
|
SUM(has_problems) as problem_weeks,
|
|
AVG(word_count) as avg_words
|
|
FROM weekly_reports
|
|
WHERE start_date >= '2026-08-01' AND start_date <= '2026-08-31'
|
|
GROUP BY YEAR(start_date), MONTH(start_date), project;
|