SELECT
root_proj.r_proj AS root_project_name ,
great_proj.g_proj AS great_project_name ,
parent_proj.p_proj AS parent_project_name ,
proj.project AS project_name,
task.task_name AS task_name,
root_proj.Time AS root_time,
great_proj.Time AS great_time ,
parent_proj.Time AS parent_time ,
proj.Time AS project_time,
task.task_time AS task_time ,
proj.Name AS user_name
FROM (
SELECT per.surname AS Name ,
p.name AS project ,
sec_to_time(sum(wi.wEnd-wi.wBegin)) AS Time ,
parent.id AS p_parent_id ,
p.id AS id
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
WHERE
di.daydate = '2009-03-09'
AND per.surName LIKE "eric%"
GROUP BY p.id ) AS proj
LEFT JOIN (
SELECT per.surname AS Name ,
parent.name AS p_proj ,
parent.parent_id AS parent_parent_id ,
parent.id AS p_id ,
sec_to_time(sum(wi.wEnd-wi.wBegin)) AS Time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
WHERE
di.daydate = '2009-03-09'
AND per.surName LIKE "eric%"
GROUP BY parent.id ) AS parent_proj
ON proj.p_parent_id = parent_proj.p_id
LEFT JOIN
(SELECT per.surname AS Name ,
great.name AS g_proj ,
great.parent_id AS great_parent_id ,
great.id AS g_id ,
sec_to_time(sum(wi.wEnd-wi.wBegin)) AS Time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
INNER JOIN T4U_PROJECTS great ON ( ifnull(parent.parent_id ,parent.id) = great.id )
WHERE
di.daydate = '2009-03-09'
AND per.surName LIKE "eric%"
GROUP BY great.id ) AS great_proj
ON parent_proj.parent_parent_id = great_proj.g_id
LEFT JOIN
(SELECT per.surname AS Name ,
root.name AS r_proj ,
root.parent_id AS root_parent_id ,
root.id AS r_id ,
sec_to_time(sum(wi.wEnd-wi.wBegin)) AS Time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
INNER JOIN T4U_PROJECTS parent ON ( ifnull(p.parent_id , p.id) = parent.id )
INNER JOIN T4U_PROJECTS great ON ( ifnull(parent.parent_id ,parent.id)= great.id )
INNER JOIN T4U_PROJECTS root ON ( ifnull(great.parent_id ,great.id) = root.id )
WHERE
di.daydate = '2009-03-09'
AND per.surName LIKE "eric%"
GROUP BY root.id ) AS root_proj
ON great_proj.great_parent_id = root_proj.r_id
RIGHT JOIN (
SELECT per.surname AS Name ,
t.name AS task_name ,
p.id AS project_id ,
sec_to_time(sum(wi.wEnd-wi.wBegin)) AS task_time
FROM T4U_WORKITEMS wi
INNER JOIN T4U_DAYINFOS di ON ( wi.dayinfo_id = di.id )
INNER JOIN T4U_TASKS t ON ( wi.task_id = t.id )
INNER JOIN T4U_PROJECTS p ON ( wi.project_id = p.id )
INNER JOIN T4U_PERSONS per ON ( di.person_id = per.id )
WHERE
di.daydate = '2009-03-09'
AND per.surName LIKE "eric%"
GROUP BY t.id ,p.id ) AS task
ON proj.id = task.project_id
相关资源：LearningSQLExample.sql
