SELECT
user_basic.id
FROM
user_basic
WHERE
id IN (
SELECT
user_basic.id
FROM
user_basic
JOIN staff ON user_basic.staff_id = staff.id
JOIN (
SELECT
*
FROM
plt_copy
WHERE
id IN (
SELECT
MAX( plt_copy.ID ) AS ID
FROM
(
SELECT
*
FROM
user_circulation
WHERE
id IN (
SELECT
user_circulation.id
FROM
(
SELECT
MAX( user_circulation.id ) AS id,
user_circulation.user_id,
user_circulation.new_staff,
user_circulation.operation_method
FROM
user_circulation
WHERE
user_circulation.operation_method != 8
GROUP BY
user_circulation.user_id
HAVING
( ( count( user_circulation.user_id ) = 1 AND user_circulation.operation_method != 5 ) OR count( user_circulation.user_id ) != 1 )
) AS user_circulation
)
) AS user_circulation
JOIN staff ON staff.id = user_circulation.new_staff
LEFT JOIN plt_copy ON ( user_circulation.user_id = plt_copy.USER_ID )
AND ( plt_copy.EMPCODE = staff.work_num )
AND plt_copy.CALL_RESULT = 1
WHERE
plt_copy.ID IS NOT NULL
GROUP BY
user_circulation.user_id
)
) AS plt_copy ON plt_copy.USER_ID = user_basic.id
WHERE
plt_copy.EMPCODE = staff.work_num
AND plt_copy.CALL_RESULT = 1
AND user_basic.staff_id IS NOT NULL
AND
CASE
WHEN ( user_basic.allot_time ) < UNIX_TIMESTAMP( plt_copy.CALL_TIME ) THEN
FALSE ELSE TRUE
END
GROUP BY
USER_ID
)
OR id IN (
SELECT
user_circulation.user_id
FROM
(
SELECT
MAX( plt_copy.ID ) AS id,
user_circulation.user_id
FROM
(
SELECT
*
FROM
user_circulation
WHERE
id IN (
SELECT
user_circulation.id
FROM
(
SELECT
MAX( user_circulation.id ) AS id,
user_circulation.user_id,
user_circulation.new_staff,
user_circulation.operation_method
FROM
user_circulation
WHERE
user_circulation.operation_method != 8
GROUP BY
user_circulation.user_id
HAVING
( ( count( user_circulation.user_id ) = 1 AND user_circulation.operation_method != 5 ) OR count( user_circulation.user_id ) != 1 )
) AS user_circulation
)
) AS user_circulation
JOIN staff ON staff.id = user_circulation.new_staff
LEFT JOIN plt_copy ON ( user_circulation.user_id = plt_copy.USER_ID )
AND ( plt_copy.EMPCODE = staff.work_num )
AND plt_copy.CALL_RESULT = 1
WHERE
plt_copy.ID IS NULL
GROUP BY
user_circulation.user_id
) AS user_circulation
)
转载于:https://my.oschina.net/guozhouyuan/blog/2252815