我在MySQL中创建了一个表。它由如下数据组成：
表 user_pack ：
id type from to user_id current_plan created_ at
1 trail 01-01-2016 05-01-2016 1 0 01-01-2016
2 free 06-01-2016 10-01-2016 1 0 06-01-2016
3 main 11-01-2016 20-01-2016 1 1 11-01-2016
4 main 21-01-2016 29-02-2016 1 1 21-01-2016
5 trail 01-01-2016 29-02-2016 2 1 01-01-2016
6 trail 01-01-2016 05-01-2016 3 0 01-01-2016
7 free 06-01-2016 29-02-2016 3 1 06-01-2016
user_id= 1 =>first register for type=trail it started from = 01-01-2016 to=05-01-2016.in that time current_plan=1 after expired current_plan=0.
=>second register for type=free it started from = 06-01-2016 to=10-01-2016 in that time current_plan=1 after expired current_plan=0.
=>third register for type=main it started from = 11-01-2016 to=20-01-2016 in that time current_plan=1 after expired current_plan=1 only
=>fourth register for type=main it started from = 21-01-2016 29-02-2016 and now it in activation current plan=1.
当我在两个日期之间搜索时01-01-2016和21-01-2016关于created_at和current计划必须1今天日期必须在表中的开始日期和结束日期之间。
我希望以这种方式输出结果：
array0=>['trail count'=>1,'free count'=>0,'main count'=>0,'date'=>01-01-2016]
array1=>['trail count'=>0,'free count'=>1,'main count'=>0,'date'=>06-01-2016]
array2=>['trail count'=>0,'free count'=>0,'main count'=>0,'date'=>11-01-2016]
array3=>['trail count'=>0,'free count'=>0,'main count'=>1,'date'=>21-01-2016]
为了能够执行这种类型的查询，您应该考虑将表拆分为 3 个不同的表：
pack_type：
- 编号
- 类型
pack_event：
- 编号
- pack_type_id
- 从
- 自
pack_registration：
- 编号
- pack_event_id
- user_id
- current_plan
- created_at
此结构将防止冗余，还使您能够进行复杂的查询。对from、to和created_at使用日期列也很重要。
使用此表结构，您将能够查询给定时间段内的所有用户注册，并使用以下查询进行current_plan=1：
select
count(pe.id) as register_count,
pt.type
from
pack_registration pr
cross join pack_type pt
left join pack_event pe on pr.pack_event_id = pe.id and pe.pack_type_id = pt.id
where
pr.current_plan = 1 and
pr.user_id = 1 and
pr.created_at between '2016-01-01' and '2016-01-31'
group by
pt.type