我写过的最长的sql语句，求大神指点，

SELECT
t. NAME AS pro_name,
sum(
(
SELECT
IFNULL(COUNT(p.id), 0) AS before_year_pro_num
FROM
project_info p
LEFT JOIN project_size s ON p.id = s.projectInfo_id
WHERE
(
DATE_FORMAT(
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
),
'%Y-%m-%d'
) < DATE_FORMAT(NOW(), '%Y-01-01')
OR (
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
) IS NULL
AND DATE_FORMAT(p.createTime, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-01-01')
)
)
AND p.state != 2
AND (s.state IS NULL OR s.state != 2)
AND (p.departmentid = t.id)
)
) AS before_year_pro_num,
sum(
(
SELECT
(
IFNULL(sum(s.size), 0) - (
SELECT
IFNULL(sum(c.clearSize), 0)
FROM
project_clear_size c
RIGHT JOIN (project_info p) ON (p.id = c.project_infoid)
WHERE
DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
AND s.state != 2
)
) AS before_year_pro_size
FROM
project_size s
RIGHT JOIN (project_info p) ON (p.id = s.projectInfo_id)
WHERE
DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
AND s.state != 2
AND (p.departmentid = t.id)
)
) AS before_year_pro_size,
sum(
(
SELECT
IFNULL(COUNT(p.id), 0) AS now_year_pro_num
FROM
project_info p
LEFT JOIN project_size s ON p.id = s.projectInfo_id
WHERE
(
(
DATE_FORMAT(
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
),
'%Y-%m-%d'
) >= DATE_FORMAT(NOW(), '%Y-01-01')
AND DATE_FORMAT(
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
),
'%Y-%m-%d'
) < DATE_FORMAT(NOW(), '%Y-%m-%d')
)
OR (
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
) IS NULL
AND (
DATE_FORMAT(p.createTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-01-01')
AND DATE_FORMAT(p.createTime, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d')
)
)
)
AND p.state != 2
AND (s.state IS NULL OR s.state != 2)
AND (p.departmentid = t.id)
)
) AS now_year_pro_num,
sum(
(
SELECT
IFNULL(SUM(s.size), 0) AS now_year_pro_size
FROM
project_size s
RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
WHERE
DATE_FORMAT(s.start_time, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-01-01')
AND DATE_FORMAT(s.start_time, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d')
AND s.state != 2
AND (p.departmentid = t.id)
)
) AS now_year_pro_size,
sum(
(
SELECT
IFNULL(COUNT(project_infoid), 0) AS now_year_clear_num
FROM
(
SELECT
*
FROM
project_clear_size c
WHERE
c.state = 1
AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-01-01')
AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
GROUP BY
c.project_infoid
) temp_clear
RIGHT JOIN (project_info p) ON (
p.id = temp_clear.project_infoid
)
WHERE
(p.departmentid = t.id)
)
) AS now_year_clear_num,
sum(
(
SELECT
IFNULL(SUM(clearSize), 0) AS now_year_clear_size
FROM
(
SELECT
*
FROM
project_clear_size c
WHERE
c.state = 1
AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-01-01')
AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
GROUP BY
c.project_infoid
) temp_clear
RIGHT JOIN project_info p ON (
p.id = temp_clear.project_infoid
)
WHERE
(p.departmentid = t.id)
)
) AS now_year_clear_size,
sum(
(
SELECT
IFNULL(COUNT(p.id), 0) AS now_month_pro_num
FROM
project_info p
LEFT JOIN project_size s ON p.id = s.projectInfo_id
WHERE
(
(
DATE_FORMAT(
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
),
'%Y-%m-%d'
) >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND DATE_FORMAT(
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
),
'%Y-%m-%d'
) < DATE_FORMAT(NOW(), '%Y-%m-%d')
)
OR (
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
) IS NULL
AND (
DATE_FORMAT(p.createTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND DATE_FORMAT(p.createTime, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d')
)
)
)
AND p.state != 2
AND (s.state IS NULL OR s.state != 2)
AND (p.departmentid = t.id)
)
) AS now_month_pro_num,
sum(
(
SELECT
IFNULL(SUM(clearSize), 0) AS now_month_clear_size
FROM
(
SELECT
*
FROM
project_clear_size c
WHERE
c.state = 1
AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
GROUP BY
c.project_infoid
) temp_clear
RIGHT JOIN project_info p ON (
temp_clear.project_infoid = p.id
)
WHERE
(p.departmentid = t.id)
)
) AS now_month_clear_size,
sum(
(
SELECT
IFNULL(COUNT(p.id), 0) AS now_date_pro_num
FROM
project_info p
LEFT JOIN project_size s ON p.id = s.projectInfo_id
WHERE
(
(
DATE_FORMAT(
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
),
'%Y-%m-%d'
) <= DATE_FORMAT(NOW(), '%Y-%m-%d')
)
OR (
(
SELECT
MIN(s.start_time)
FROM
project_size
WHERE
s.projectInfo_id = p.id
AND s.state != 2
) IS NULL
AND DATE_FORMAT(p.createTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
)
)
AND p.state != 2
AND (s.state IS NULL OR s.state != 2)
AND (p.departmentid = t.id)
)
) AS now_date_pro_num,
sum(
(
SELECT
(
IFNULL(sum(s.size), 0) - (
SELECT
IFNULL(sum(c.clearSize), 0)
FROM
project_clear_size c
RIGHT JOIN project_info p ON (c.project_infoid = p.id)
WHERE
DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
AND s.state != 2
AND (p.departmentid = t.id)
)
) AS now_date_pro_size
FROM
project_size s
RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
WHERE
DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
AND s.state != 2
AND (p.departmentid = t.id)
)
) AS now_date_pro_size,
sum(
(
SELECT
(
(
未完待续.....

IFNULL(sum(s.size), 0) - (
                        SELECT
                            IFNULL(sum(c.clearSize), 0)
                        FROM
                            project_clear_size c
                        RIGHT JOIN project_info p ON (c.project_infoid = p.id)
                        WHERE
                            DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                        AND s.state != 2
                        AND (p.departmentid = t.id)
                    )
                ) - (
                    SELECT
                        IFNULL(sum(s.size), 0) - (
                            SELECT
                                IFNULL(sum(c.clearSize), 0)
                            FROM
                                project_clear_size c
                            RIGHT JOIN project_info p ON (c.project_infoid = p.id)
                            WHERE
                                DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
                            AND s.state != 2
                            AND (p.departmentid = t.id)
                        )
                    FROM
                        project_size s
                    RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
                    WHERE
                        DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
                    AND s.state != 2
                    AND (p.departmentid = t.id)
                )
            ) AS now_beforeYear_money
        FROM
            project_size s
        RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
        WHERE
            DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
        AND s.state != 2
        AND (p.departmentid = t.id)
    )
) AS now_beforeYear_money,
CONCAT(
    FORMAT(
        (
            sum(
                (
                    SELECT
                        IFNULL(
                            (
                                (
                                    (
                                        IFNULL(sum(s.size), 0) - (
                                            SELECT
                                                IFNULL(sum(c.clearSize), 0)
                                            FROM
                                                project_clear_size c
                                            RIGHT JOIN project_info p ON (c.project_infoid = p.id)
                                            WHERE
                                                DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                                            AND c.state != 2
                                            AND (p.departmentid = t.id)
                                        )
                                    ) - (
                                        SELECT
                                            IFNULL(sum(s.size), 0) - (
                                                SELECT
                                                    IFNULL(sum(c.clearSize), 0)
                                                FROM
                                                    project_clear_size c
                                                RIGHT JOIN project_info p ON (c.project_infoid = p.id)
                                                WHERE
                                                    DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
                                                AND c.state != 2
                                            )
                                        FROM
                                            project_size s
                                        RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
                                        WHERE
                                            DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
                                        AND s.state != 2
                                        AND (p.departmentid = t.id)
                                    )
                                ) / (
                                    SELECT
                                        IFNULL(sum(s.size), 0) - (
                                            SELECT
                                                IFNULL(sum(c.clearSize), 0)
                                            FROM
                                                project_clear_size c
                                            RIGHT JOIN project_info p ON (c.project_infoid = p.id)
                                            WHERE
                                                DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
                                            AND c.state != 2
                                            AND (p.departmentid = t.id)
                                        )
                                    FROM
                                        project_size s
                                    RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
                                    WHERE
                                        DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-01-01')
                                    AND s.state != 2
                                    AND (p.departmentid = t.id)
                                )
                            ),
                            0
                        ) AS now_beforeYear_rate
                    FROM
                        project_size s
                    RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
                    WHERE
                        DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                    AND s.state != 2
                    AND (p.departmentid = t.id)
                )
            ) * 100
        ),
        2
    ),
    '%'
) AS now_beforeYear_rate,
CONCAT(
    (
        FORMAT(
            (
                (
                    sum(
                        (
                            SELECT
                                (
                                    IFNULL(sum(s.size), 0) - (
                                        SELECT
                                            IFNULL(sum(c.clearSize), 0)
                                        FROM
                                            project_clear_size c
                                        RIGHT JOIN project_info p ON (c.project_infoid = p.id)
                                        WHERE
                                            DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                                        AND s.state != 2
                                        AND (p.departmentid = t.id)
                                    )
                                ) AS now_date_pro_size
                            FROM
                                project_size s
                            RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
                            WHERE
                                DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                            AND s.state != 2
                            AND (p.departmentid = t.id)
                        )
                    ) / (
                        SELECT
                            (
                                IFNULL(sum(s.size), 0) - (
                                    SELECT
                                        IFNULL(sum(c.clearSize), 0)
                                    FROM
                                        project_clear_size c
                                    WHERE
                                        DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                                    AND s.state != 2
                                )
                            ) AS now_date_pro_size
                        FROM
                            project_size s
                        WHERE
                            DATE_FORMAT(s.start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                        AND s.state != 2
                    )
                ) * 100
            ),
            2
        )
    ),
    '%'
) AS size_rate,
(
    SELECT
        IFNULL(SUM(s.size), 0) AS now_month_pro_size
    FROM
        project_size s
    RIGHT JOIN project_info p ON (s.projectInfo_id = p.id)
    WHERE
        DATE_FORMAT(s.start_time, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-01')
    AND DATE_FORMAT(s.start_time, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d')
    AND s.state != 2
    AND p.departmentid = t.id
) AS now_month_pro_size,
(
    SELECT
        IFNULL(COUNT(temp_clear.id), 0)
    FROM
        (
            SELECT
                *
            FROM
                project_clear_size c
            WHERE
                c.state = 1
            AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-01')
            AND DATE_FORMAT(c.clearTime, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
            GROUP BY
                c.project_infoid
        ) temp_clear
    RIGHT JOIN project_info p ON (
        temp_clear.project_infoid = p.id
    )
    WHERE
        p.departmentid = t.id
) AS now_month_clear_num

FROM
t_department t
WHERE
t.state != 2
GROUP BY
t.id

额滴个神啊，你是想说让人给优化一下吗？

放到数据库中继续操作吧（视图或存储过程），在程序里面用的话不是很好