带有 dayValue 的 SQL 数据透视表 [处理数据透视表中的日期] MYSQL-解决方案 [英] SQL pivot with dayValue [dealing with date in pivot-table] MYSQL-solution

问题描述

我有以下数据库:

           ...
           `id` int(8) NOT NULL auto_increment,
           `title` varchar(60) NOT NULL default '',
           `date_firstcall` timestamp NOT NULL default CURRENT_TIMESTAMP,
           `cid` int(8) NOT NULL default '1',
           ...

通过使用此 SQL 语句:

By using this SQL-Statement:

SELECT apv.title,apv.date_firstcall, COUNT( date_firstcall ) AS totalViews, DAYNAME(apv.date_firstcall) AS Tag FROM AnalysePageview apv  GROUP BY TO_DAYS(date_firstcall), apv.title ORDER BY apv.date_firstcall DESC

我会得到一张这样的桌子:

I will get a table like this:

title   date_firstcall      totalViews  Tag
name A  2014-08-25 10:19:49     2   Monday
name B  2014-08-25 07:04:36     2   Monday
name B  2014-08-24 16:03:36     3   Sunday
name C  2014-08-24 14:54:47     2   Sunday
name D  2014-08-24 10:25:35     1   Sunday
name A  2014-08-24 00:01:45     24  Sunday
name C  2014-08-23 11:06:19     3   Saturday
name A  2014-08-23 00:05:35     16  Saturday
name B  2014-08-22 10:05:53     4   Friday
name A  2014-08-22 00:11:28     25  Friday
name C  2014-08-21 19:28:54     1   Thursday
name A  2014-08-21 08:44:05     13  Thursday
name C  2014-08-20 22:42:49     1   Wednesday
name E  2014-08-20 03:04:03     1   Wednesday
name A  2014-08-20 00:25:01     23  Wednesday
name C  2014-08-19 16:27:03     2   Tuesday
name D  2014-08-19 16:22:42     2   Tuesday
name A  2014-08-19 15:43:57     10  Tuesday
name B  2014-08-19 09:36:52     1   Tuesday
name E  2014-08-18 20:31:06     1   Monday
name C  2014-08-18 18:51:15     19  Monday
name B  2014-08-18 17:52:21     4   Monday
name D  2014-08-18 14:55:52     3   Monday

我想要从今天开始的最后一周(今天是星期一；):

I would like to get a table witch shout show the last week beginning with today (today is Monday;) :

       Monday  Sunday  Saturday  Friday  ...
name A   2       24      0
name B   2       3

是否有可能获得不同语言的日期名称?

and is there any possibility to get the day-names in a different language?

请在将其标记为双重之前:我已经看过其他关键问题，没有人在处理可变的日期格式.

Please before you mark it as double: I have seen the other pivot questions, no one is dealing with a variable day-format.

推荐答案

我找到了解决方案:

首先，我制作了一个帮助表，而不是 pivot-sql 语句.

First I made a helping-table than the pivot-sql-statement.

          $sql = "DROP VIEW IF EXISTS tmp";
          $result = mysqli_query($link, $sql);
          $sql =  "CREATE VIEW tmp AS SELECT apv.title,apv.date_firstcall,
                   COUNT( date_firstcall ) AS totalViews, 
                   DAYNAME(apv.date_firstcall) AS Tag FROM AnalysePageview apv 
                   WHERE apv.date_firstcall > CURRENT_DATE - INTERVAL 6 DAY 
                   GROUP BY TO_DAYS(date_firstcall), 
                   apv.title ORDER BY apv.date_firstcall DESC";

          $result = mysqli_query($link, $sql);

          $sql =  "SELECT title as Seite,
                    sum( if( Tag = 'Monday', totalViews, 0 ) ) AS Montag,
                    sum( if( Tag = 'Tuesday', totalViews, 0 ) ) AS Dienstag,
                    sum( if( Tag = 'Wednesday', totalViews, 0 ) ) AS Mittwoch,
                    sum( if( Tag = 'Thursday', totalViews, 0 ) ) AS Donnerstag,
                    sum( if( Tag = 'Friday', totalViews, 0 ) ) AS Freitag,
                    sum( if( Tag = 'Saturday', totalViews, 0 ) ) AS Samstag,
                    sum( if( Tag = 'Sunday', totalViews, 0 ) ) AS Sonntag
                    FROM tmp  GROUP BY title";

这篇关于带有 dayValue 的 SQL 数据透视表 [处理数据透视表中的日期] MYSQL-解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！