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
(
(
未完待续.....
我写过的最长的sql语句,求大神指点,
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
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系统签名