mysql - 使用内部连接计算特定字段

Question

我有以下架构:

create table myapp_task
(
  title varchar(100) not null,
  state varchar(11) not null,
  estimate date not null,
  my_id int not null auto_increment
  primary key,
  road_map_id int not null,
  create_date date not null,
  constraint myapp_task_road_map_id_5e114978_fk_myapp_roadmap_rd_id
  foreign key (road_map_id) references myapp_roadmap (rd_id)
);
— auto-generated definition
create table myapp_roadmap
(
  rd_id int not null auto_increment
  primary key,
  name varchar(50) not null
);

我想要获取编号、创建日期一周的开始和结束、所有任务的数量和就绪任务的数量(state = 'ready/in_progress')

这是我的查询:

select DISTINCT week(create_date, 1) as week,
  SUBDATE(create_date, WEEKDAY(create_date)) as beginofweek,
  DATE(create_date + INTERVAL (6 - WEEKDAY(create_date)) DAY) as endofweek,
  SUM(state) as number,
  SUM(state = 'ready') as ready
from myapp_task 
inner join myapp_roadmap
  on myapp_task.road_map_id = myapp_roadmap.rd_id;

实际上，我只有就绪任务的数量有问题。

Answer 1

我认为你很接近:

select week(create_date, 1) as week,
    SUBDATE(create_date, WEEKDAY(create_date)) as beginofweek,
    DATE(create_date + INTERVAL (6 - WEEKDAY(create_date)) DAY) as endofweek,
    count(state) as number,
    SUM(CASE WHEN state = 'ready' THEN 1 ELSE 0 END) as ready,
    SUM(CASE WHEN state = 'in_progress' THEN 1 ELSE 0 END) as in_progress
FROM myapp_task inner join myapp_roadmap
    on myapp_task.road_map_id = myapp_roadmap.rd_id
GROUP BY week, beginofweek, endofweek

使用 CASE 语句，您可以单独添加状态，即 ready 或 in_progress。此外，添加 GROUP BY 可确保计数是针对一周的。我认为在这种情况下，MySQL 可能会在没有 GROUP BY 的情况下吐出正确的结果，但为什么让它在这里猜测你想要什么。此外，如果您升级到 MySQL 5.7+，那么像这样没有 GROUP BY 的查询将默认出错。

---

还去掉了 DISTINCT 修饰符。谢谢@AaronDietz