如何在单个查询中使用不同的user_id获取计数，类型和日期

Question

I created a table in MySQL. It consists of data like this:

Table 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.

When I search in between two dates 01-01-2016 and 21-01-2016 with respect to created_at and current plan must be 1 and today date must be in between from and to date in table.

I want the output result in this way:

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]

Answer 1

To be able to perform this type of query you should consider to split up your table into 3 different tables:

pack_type:

• id
• type

pack_event:

• id
• pack_type_id
• from
• to

pack_registration:

• id
• pack_event_id
• user_id
• current_plan
• created_at

This structure will prevent redundancy and also enables you to make sophisticated queries. It is also important to use date columns for from, to and created_at.

With this table structure you will be able to query all user registrations for the given time period and current_plan=1 with the following query:

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