计数与内具体领域加入
问题描述:
我有以下模式:计数与内具体领域加入
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
);
我想拿到号,开始和CREATE_DATE一周的结束,所有的任务数,(的就绪任务数的状态=“准备/ IN_PROGRESS')
这里是我的查询:
其实,我有一个问题只与准备任务数。
答
我觉得你接近:
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
语句,您可以加起来只states
是单独ready
或in_progress
。此外,增加一个GROUP BY确保计数是在一周内。我认为在这种情况下,MySQL可能会在没有GROUP BY的情况下吐出正确的结果,但为什么要让它猜到你想要的内容。另外,如果您升级到MySQL 5.7+,那么默认情况下,没有GROUP BY编写的这样的查询将会出错。
也摆脱了那个DISTINCT修饰符。由于@AaronDietz
答
你应该查找使用aggregate functions
COUNT
是返回的行数的功能,并获得了国家总数两个值和那些等于“准备好” ,您需要使用不同的连接条件连接表格两次。
然后未汇总的列需要包含在GROUP BY
子句中。
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,
COUNT(r1.state) AS number,
COUNT(r2.state) AS ready
from myapp_roadmap inner join myapp_task r1
on r1.road_map_id = myapp_roadmap.rd_id
inner join myapp_task r2
on r2.road_map_id = myapp_roadmap.rd_id and r2.state = 'ready'
group by week, beginofweek, endofweek
这真的是一个很大的要求,指定什么是你的查询问题? – Shadow