Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
274 views
in Technique[技术] by (71.8m points)

select - Mysql show data in Pivot View

Sorry Guys, could not generate a fiddle since facing some issues with sqlfiddle.com

I have a table in mysql database which is as follows:

Username    mailtime    mailid
User4   3/23/2013 10:26 4473
User1   4/6/2013 16:13  10934
User4   4/6/2013 17:17  10957
User1   4/6/2013 23:56  11092
User2   4/7/2013 11:58  11187
User1   4/7/2013 12:06  11190
User4   4/7/2013 13:11  11216
User4   4/7/2013 13:14  11217
User1   4/7/2013 14:40  11245
User5   4/7/2013 15:52  11259
User1   4/7/2013 18:12  11303
User5   4/7/2013 19:56  11323
User1   4/7/2013 22:52  11358
User4   4/8/2013 11:13  11465
User1   4/8/2013 11:20  11475
User1   4/8/2013 11:35  11491
User4   4/8/2013 12:10  11511
User4   4/8/2013 12:38  11532
User4   4/8/2013 12:51  11540
User4   4/8/2013 13:06  11551
User1   4/8/2013 13:09  11552
User4   4/8/2013 13:15  11560
User1   4/8/2013 13:24  11572
User1   4/8/2013 14:01  11614
User4   4/8/2013 14:27  11640
User1   4/8/2013 15:41  11700
User5   4/8/2013 16:04  11730
User1   4/8/2013 17:40  11814
User4   4/9/2013 11:16  12117
User1   4/9/2013 12:41  12198
User1   4/9/2013 12:59  12209
User4   4/9/2013 13:58  12243
User4   4/9/2013 14:05  12250
User1   4/9/2013 14:15  12256
User4   4/9/2013 16:51  12351
User1   4/9/2013 17:33  12397
User1   4/9/2013 19:01  12455
User4   4/9/2013 19:15  12463
User5   4/9/2013 20:59  12517
User1   4/9/2013 21:26  12530
User1   4/9/2013 22:46  12561
User1   4/10/2013 1:01  12595
User1   4/10/2013 8:42  12631
User1   4/10/2013 10:18 12663
User1   4/10/2013 11:21 12697
User3   4/10/2013 11:27 12701
User4   4/10/2013 11:34 12705
User1   4/10/2013 15:26 12856
User4   4/10/2013 16:51 12909
User2   4/10/2013 16:53 12913

The output that i require is as follows:

Username    < 5 days    6-Apr   7-Apr   8-Apr   9-Apr   10-Apr  Grand Total
User1                           
User2                           
User3                           
User4                           
User5                           
Grand Total                         

Not sure how to do this.

The max mailtime is displayed in the 2nd last column and previous days in the previous columns and < 5 days is cumilative of all the other days

EDIT::

The data that is required is the count of mailids

The columns < 5 days, 6-Apr, 7-Apr, 8-Apr, 9-Apr and 10-Apr are the columns from mailtime Column. If the max date from the column is say 25th Mar, the the columns should be < 5 days, 20-Mar, 21-Mar, 22-Mar, 23-Mar, 24 Mar and 25-Mar

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Your description of what you want for the desired the result is not exactly clear but it seems like you can use the following to get the result. This get the total number of rows per username on each of the previous 5 days (based on the max date) as well as the total number of rows for each user before this 5 day period:

select 
  coalesce(username, 'Grand Total') username,
  max(`< 5 days`) `< 5 days`,
  sum(case when maildate = '6-Apr' then 1 else 0 end) `6-Apr`,
  sum(case when maildate = '7-Apr' then 1 else 0 end) `7-Apr`,
  sum(case when maildate = '8-Apr' then 1 else 0 end) `8-Apr`,
  sum(case when maildate = '9-Apr' then 1 else 0 end) `9-Apr`,
  sum(case when maildate = '10-Apr' then 1 else 0 end) `10-Apr`,
  count(*) GrandTotal
from
(
  select c.username,
    date_format(c.mailtime, '%e-%b') maildate,
    coalesce(o.`< 5 days`, 0) `< 5 days`
  from yt c
  left join
  (
    select username,
      count(*) `< 5 days`
    from yt
    where mailtime <= (select date_sub(max(mailtime), interval 4 DAY)
                        from yt)
  ) o
    on c.username = o.username
  where c.mailtime >= (select date_sub(max(mailtime), interval 4 DAY)
                          from yt)
) d
group by username with rollup;

See SQL Fiddle with Demo.

I wrote a hard-coded version so you could see how the code with be written but if you are basing the data off the max(mailtime) then you will most likely want to use dynamic SQL to get the result. You can use a prepared statement to generate the SQL string that will be executed:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN maildate = ''',
      date_format(mailtime, '%e-%b'),
      ''' THEN 1 else 0 END) AS `',
      date_format(mailtime, '%e-%b'), '`'
    )
  ) INTO @sql
FROM yt
WHERE mailtime >= (select date_sub(max(mailtime), interval 4 DAY)
                   from yt);

SET @sql 
  = CONCAT('SELECT coalesce(username, ''Grand Total'') username,
              max(`< 5 days`) `< 5 days`, ', @sql, ' ,
              count(*) GrandTotal
            from
            (
              select c.username,
                date_format(c.mailtime, ''%e-%b'') maildate,
                coalesce(o.`< 5 days`, 0) `< 5 days`
              from yt c
              left join
              (
                select username,
                  count(*) `< 5 days`
                from yt
                where mailtime <= (select date_sub(max(mailtime), interval 4 DAY)
                                    from yt)
              ) o
                on c.username = o.username
              where c.mailtime >= (select date_sub(max(mailtime), interval 4 DAY)
                                      from yt)
            ) d
            group by username with rollup ');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both queries give the result:

|    USERNAME | < 5 DAYS | 6-APR | 7-APR | 8-APR | 9-APR | 10-APR | GRANDTOTAL |
--------------------------------------------------------------------------------
|       User1 |        0 |     1 |     4 |     7 |     7 |      5 |         24 |
|       User2 |        0 |     0 |     1 |     0 |     0 |      1 |          2 |
|       User3 |        0 |     0 |     0 |     0 |     0 |      1 |          1 |
|       User4 |        2 |     1 |     2 |     7 |     5 |      2 |         17 |
|       User5 |        0 |     0 |     2 |     1 |     1 |      0 |          4 |
| Grand Total |        2 |     2 |     9 |    15 |    13 |      9 |         48 |

If this not the result that you want, then you will have to further explain your need.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...