mysql - 将 2 个具有不同列数的 select 语句组合在一起，其中第一个语句已使用 JOIN

Question

我正在尝试将 2 个具有不同列数的选择语句组合起来。

第一个语句是这样的:

SELECT s.id, s.date_sent, m.sam_subject, m.sam_msg_id, (SELECT
COUNT(id) FROM tbl_something WHERE schedule_id = s.id) AS
total_recipients FROM tbl_something2 AS s INNER JOIN
tbl_something3 AS m ON s.message_id = m.sam_msg_id ORDER BY
s.date_sent DESC

第二条语句:

SELECT * FROM sms_something4 WHERE status = '0' ORDER BY id DESC

第一个语句的表输出:

id date_sent sam_subject sam_msg_id total_recipients

1 1372880628 e-Newsletter 2 2

第二个输出:

id | subject | sent | failed | date_sent | data_sent | data_failed | message | sam_uid from | select_members | status | from_no
11 | test    |  2   |  0     |  1372881670 | 639176286411,639224588324 | | | | |                             | 0      | 0

关于如何结合这两个语句有什么建议吗？

我的目标输出是

id | subject | sent | failed | date_sent | sam_subject | total_recipients | date_sent for email

sam_msg_id 可以忽略。

谢谢。

Answer 1

这是您需要具备的基本知识..您可能需要排除故障。根据需要添加列。

SELECT *
FROM (
SELECT s.id, s.date_sent, m.sam_subject, m.sam_msg_id, (SELECT COUNT(id) 
                                                    FROM tbl_something 
                                                    WHERE schedule_id = s.id) AS total_recipients 
FROM tbl_something2 AS s 
INNER JOIN tbl_something3 AS m 
    ON s.message_id = m.sam_msg_id 
) as tbl
INNER JOIN (SELECT * FROM sms_something4 WHERE status = '0') as tbl2
    ON tbl2.subject = tbl.sam_subject
    and tbl.date_sent=tbl2.date_sent
    and tbl.total_Recipients = tbl2.sent+ tbl2.failed
ORDER BY tbl.date_sent DESC