u011403963 2015-08-05 10:12 采纳率: 0%
浏览 1391

我写过的最长的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
(
(
未完待续.....

  • 写回答

3条回答 默认 最新

  • u011403963 2015-08-05 10:14
    关注
    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

    评论

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图的问题
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名