MySQL在数据透视视图中显示数据 [英] Mysql show data in Pivot View
问题描述
对不起，由于遇到sqlfiddle.com的一些问题，所以无法产生提要.
Sorry Guys, could not generate a fiddle since facing some issues with sqlfiddle.com
我在mysql数据库中有一个表，如下所示:
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
我需要的输出如下:
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.
最长邮件时间显示在倒数第二个列中，而前几天则显示在前几天中，<所有其他日子累计为5天
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
:
所需的数据是邮件的数量
The data that is required is the count of mailids
列< mailtime列中的列为5天，6月4日，7月4月，8月4日，9月4日和10月4日.如果该列的最长日期为3月25日，则这些列应为< 5天，3月20日，3月21日，3月22日，3月23日，3月24日和3月25日
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
推荐答案
您对所需结果的描述并不十分清楚，但似乎可以使用以下方法获得结果.这将获得前5天(基于最长日期)中每个用户名的总行数，以及这5天之前每个用户的总行数:
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;
请参见带有演示的SQL小提琴.
我写了一个硬编码版本，因此您可以看到如何编写代码，但是如果您将数据基于max(mailtime)，那么您很可能希望使用动态SQL来获得结果.您可以使用准备好的语句来生成将要执行的SQL字符串:
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;
请参见带演示的SQL小提琴.这两个查询都给出结果:
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.
这篇关于MySQL在数据透视视图中显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
