我有一个创建数据透视表的MySQL SELECT。
SELECT t.*,
sum(HOUR(TIMEDIFF(STR_TO_DATE(Arbeitsbeginn, '%d.%m.%Y %H:%i:%s'),
STR_TO_DATE(Arbeitsende, '%d.%m.%Y %H:%i:%s')))) AS ARBEITSZEIT,
sum(HOUR(TIMEDIFF(STR_TO_DATE(Projektbeginn, '%d.%m.%Y %H:%i:%s'),
STR_TO_DATE(Projektende, '%d.%m.%Y %H:%i:%s')))) AS Projektzeit
FROM (SELECT DriverName,
Baustelle,
Arbeitsbeginn,
Arbeitsende,
Projektbeginn,
Projektende
FROM (SELECT DRIVERNAME,
GROUP_CONCAT(IF(ACTIONTEXT = 'PB', AREA, NULL)) AS 'Baustelle',
GROUP_CONCAT(IF(ACTIONTEXT = 'Arbeitsbeginn', DATETIME, NULL)) AS 'Arbeitsbeginn',
GROUP_CONCAT(IF(ACTIONTEXT = 'Arbeitsende', DATETIME, NULL)) AS 'Arbeitsende',
GROUP_CONCAT(IF(ACTIONTEXT = 'PB', DATETIME, NULL)) AS 'Projektbeginn',
GROUP_CONCAT(IF(ACTIONTEXT = 'PE', DATETIME, NULL)) AS 'Projektende'
FROM geoImportRoot
GROUP BY DRIVERNAME
) A CROSS JOIN
(SELECT 1 AS n UNION ALL SELECT 2) n
) t
GROUP BY DriverName;
检查这个小提琴:选择
问题是,我需要按日期分隔的数据,现在我把它们都排成一行:
2013年6月19日09:09:01、2013年6日19日10:29:28、2013年12月19日14:01:11、2013年2月20日10:01:00
希望你能帮我解决这个问题。如果可能,请使用SQL Fiddle
您不应该将日期/时间值存储为字符串。为此,您应该在内置数据类型中使用。
在任何情况下,数据都是字符串的前10个字符。以下变化按天划分数据:
SELECT t.*,
sum(HOUR(TIMEDIFF(STR_TO_DATE(Arbeitsbeginn, '%d.%m.%Y %H:%i:%s'), STR_TO_DATE(Arbeitsende, '%d.%m.%Y %H:%i:%s')))) AS ARBEITSZEIT,
sum(HOUR(TIMEDIFF(STR_TO_DATE(Projektbeginn, '%d.%m.%Y %H:%i:%s'), STR_TO_DATE(Projektende, '%d.%m.%Y %H:%i:%s')))) AS Projektzeit
FROM (SELECT DriverName, thedate,
Baustelle,
Arbeitsbeginn,
Arbeitsende,
Projektbeginn,
Projektende
FROM (SELECT DRIVERNAME, left(`datetime`, 10) as thedate,
GROUP_CONCAT(IF(ACTIONTEXT = 'PB', AREA, NULL)) AS 'Baustelle',
GROUP_CONCAT(IF(ACTIONTEXT = 'Arbeitsbeginn', DATETIME, NULL)) AS 'Arbeitsbeginn',
GROUP_CONCAT(IF(ACTIONTEXT = 'Arbeitsende', DATETIME, NULL)) AS 'Arbeitsende',
GROUP_CONCAT(IF(ACTIONTEXT = 'PB', DATETIME, NULL)) AS 'Projektbeginn',
GROUP_CONCAT(IF(ACTIONTEXT = 'PE', DATETIME, NULL)) AS 'Projektende'
FROM geoImportRoot
GROUP BY DRIVERNAME, left(`datetime`, 12)
) A CROSS JOIN
(SELECT 1 AS n UNION ALL SELECT 2) n
) t
GROUP BY DriverName, thedate;