Good day!

I have a table, let's call it conditionally "auth". Its structure is simplified to such a form that it is clearer:

id INT(11) PK AUTO_INCREMENT user_id INT(11) date DATETIME

Suppose it is recorded user authorization (user_id) in a certain system, and the date of authorization (date). One user can have multiple authorizations, one-to-many relationship.

My task is using MySQL tools to display a report on the selected time interval (from and to) of the following form:

 user | 2015-01-01 | 2015-01-02 | 2015-01-03 | | | | | x | 2 | 0 | 3 | y | 0 | 1 | 1 | z | 0 | 0 | 1 |

In this example, the number of user authorizations x, y and z (user_id IN (x, y, z)) is requested, for the period from 2015-01-01 to 2015-01-03 (date> = '2015-01-01 00:00:00 'AND date <=' 2015-01-03 23:59:59 ')

How can I derive a multidimensional result and place the report dates “horizontally”, while substituting the missing dates (or at least without them)?

This is how I can display the number of authorizations, but only for one user (x):

 SELECT COUNT(*) AS CNT, DATE_FORMAT(`date`, '%Y-%m-%d') as DATE FROM auth WHERE user_id = x GROUP BY DATE ORDER BY DATE DESC;

But how to do this for multiple users, as in the example? Really for each user it is necessary to make nested select on the number of authorizations by dates?

Thank!

  • But how to do this for several users - obviously, it is necessary to remove the restriction on user_id . less obvious - add user_id to the grouping list. approximately: select ... from auth group by user_id, date order by ... - aleksandr barakin

1 answer 1

But how to do this for multiple users

obviously, it is necessary to remove the restriction on user_id . less obvious - add user_id to the grouping list. see first query below.

How can I derive a multidimensional result and place the report dates “horizontally”, while substituting the missing dates (or at least without them)?

for example, as in the second query below. To form columns dynamically is better not with sql tools (this is quite inefficient and very cumbersome), but with the tools of a higher, more suitable language for this.

SQL feeddle

MySQL 5.6 Schema Setup :

 create table auth (u int, d datetime); insert into auth values (1, "2015-11-04 17:00") ,(1, "2015-11-05 18:00") ,(1, "2015-11-05 19:00") ,(2, "2015-11-04 20:00") ,(2, "2015-11-04 21:00") ;

Query 1 :

 select u ,count(*) as cnt ,date(d) as date from auth group by u, date order by date desc

Results :

 | u | cnt | date | |---|-----|----------------------------| | 1 | 2 | November, 05 2015 00:00:00 | | 1 | 1 | November, 04 2015 00:00:00 | | 2 | 2 | November, 04 2015 00:00:00 |

Query 2 :

 select u ,sum(date(d) = "2015-11-04") as `2015-11-04` ,sum(date(d) = "2015-11-05") as `2015-11-05` from auth group by u

Results :

 | u | 2015-11-04 | 2015-11-05 | |---|------------|------------| | 1 | 1 | 2 | | 2 | 2 | 0 |
  • thanks for the answer. without parsing into the desired kind of query result by a higher language, it did not go all the same. - Matvey Safronov