左连接where-----------------
SELECT
e1.real_exchange_rmb,
IFNULL(c.channel, 1) AS promote_channel
FROM
(
SELECT
e.real_exchange_rmb,
e.rid,
IFNULL(r.regist_channel, 1) AS promote_channel
FROM
game_apply_exchange_order AS e
LEFT JOIN game_registin AS r ON e.rid = r.user_id
WHERE
e.apply_time >= DATE_FORMAT(
DATE_SUB(CURDATE(), INTERVAL 1 DAY),
'%Y-%m-%d %H:%i:00'
)
AND e.apply_time < DATE_FORMAT(
DATE_SUB(CURDATE(), INTERVAL 0 DAY),
'%Y-%m-%d %H:%i:00'
)
AND e.manual_op_state IN (1, 6)
) AS e1
LEFT JOIN web_count AS c ON e1.promote_channel = c.channel
WHERE
c.compute_time = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
-------------------------------------下面的才是对的----------
SELECT
e1.real_exchange_rmb,
IFNULL(c.channel, 1) AS promote_channel
FROM
(
SELECT
e.real_exchange_rmb,
e.rid,
IFNULL(r.regist_channel, 1) AS promote_channel
FROM
game_apply_exchange_order AS e
LEFT JOIN game_registin AS r ON e.rid = r.user_id
WHERE
e.apply_time >= DATE_FORMAT(
DATE_SUB(CURDATE(), INTERVAL 1 DAY),
'%Y-%m-%d %H:%i:00'
)
AND e.apply_time < DATE_FORMAT(
DATE_SUB(CURDATE(), INTERVAL 0 DAY),
'%Y-%m-%d %H:%i:00'
)
AND e.manual_op_state IN (1, 6)
) AS e1
LEFT JOIN (
SELECT
channel
FROM
web_count
WHERE
compute_time = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
) AS c ON e1.promote_channel = c.channel