mysql - 从 2 个不同的表中选择多列并对所选列进行计数

Question

你能教我并帮助我了解我的 mysql 代码吗？问题如下。

查询A

SELECT gbp_sender_date,COUNT(*) AS total_post
FROM guestbook_post
GROUP BY gbp_sender_date DESC;

查询B

SELECT v_date, COUNT(*) AS total_view
FROM visitor_detail_2 
GROUP BY v_date DESC;

查询C

SELECT v.v_date, g.gbp_sender_date
FROM visitor_detail_2 v
LEFT JOIN guestbook_post g ON v.v_date = g.gbp_sender_date
GROUP BY v.v_date DESC;

输出查询A

----------------------------
gbp_sender_date| total post |

----------------------------
2019-04-09     |    5

2019-04-08     |    22   

2019-04-05     |     5

2019-04-04     |     5

2019-04-03     |     5

2019-04-02     |     5

2019-04-01     |     5

-----------------------------

输出查询B

----------------------------
v_date         | total view

----------------------------
2019-04-09     |     9

2019-04-08     |     8

2019-04-05     |     5

2019-04-04     |     4

2019-04-03     |     3

2019-04-02     |     2

2019-04-01     |     1

-----------------------------

问题是如何更改查询 C 中的代码，以便获得这样的预期输出

---------------------------------------------------------
gbp_sender_date| total post |   v_date       | total view

---------------------------------------------------------
2019-04-09     |     5      | 2019-04-09     |     9      

2019-04-08     |    22      | 2019-04-08     |     8      

2019-04-05     |     5      | 2019-04-05     |     5      

2019-04-04     |     5      | 2019-04-04     |     4      

2019-04-03     |     5      | 2019-04-03     |     3      

2019-04-02     |     5      | 2019-04-02     |     2      

2019-04-01     |     5      | 2019-04-01     |     1      

----------------------------------------------------------

Answer 1

从查询 C 获取所需结果的一种方法是将查询 A 和查询 B 一起JOIN:

SELECT g.*, v.*
FROM (SELECT gbp_sender_date,COUNT(*) AS total_post
      FROM guestbook_post
      GROUP BY gbp_sender_date) g
JOIN (SELECT v_date, COUNT(*) AS total_view
      FROM visitor_detail_2 
      GROUP BY v_date) v ON v.v_date = g.gbp_sender_date
ORDER BY g.gbp_sender_date DESC