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
分享到:
相关推荐
Proc SQL by Example, Using SQL within SAS
英文版Oracle PL/SQL by Example(4th Edition)
Oracle® PL/SQL™ by Example, Third Edition By Benjamin Rosenzweig, Elena Silvestrova Publisher : Prentice Hall PTR Pub Date : September 10, 2003 ISBN : 0-13-117261-1 Pages : 768
SQL表明明有数据,为何SqlDataReader reader = command.ExecuteReader();reader.HasRow返回false? 论坛问题C#代码
An introduction to SQL for the absolute beginner. Learn how to create and query databases, add and remove data from tables, set constraints, create stored procedures, and more
Oracle SQL By Example 4th Edition by Alice Rischert - 5 Star Book Review.pdf
PL.SQL.By.Example,是oracle数据库plsql编程的实例脚本,值得一看。
An introduction to SQL for the absolute beginner. Learn how to create and query databases, add and remove data from tables, set constraints, create stored procedures, and more
一本学习ORACLE PL/SQL不可多得好书! 英语原版,高清晰。 多多学习,多多交流!
一个SQL Server数据库操作通用类example_ DBHelper
英文原版 Start developing applications with Oracle PL/SQL
后端拿到请求参数需要进行sql语句拼接,由于mybatis-generator的example类存在较多问题,对其进行改进: ...2.保证业务代码中不出现sql语句 3.可以生成嵌套的or条件语句 4.实现良好的代码提示不补全
There are a lot of materials mention the hr_main.sql for test, but I can't find it in the $ORACLE_HOME/demo/schema/human_resources/; so enjoy it for you and wish to help U
Oracle PL_SQL by Example,PL_SQL 案例在用jdbcs时可以参考
实例学pl/sql,很独到,很实用,配套资源可上http://www.informit.com/store/product.aspx?isbn=0137144229下载,好好练两遍,pl/sql就成了
SQL JDBC example ,if need ,please downlaod
An example of Linq to Sql by C#.
An introduction to SQL for the absolute beginner. Learn how to create and query databases, add and remove data from tables, set constraints, create stored procedures, and more
arctutor的示例数据,关于sql 例子的示例空间数据库