时间戳

首页 > 解决方案 > 合并 2 个 SQL 查询。

mysql - 合并 2 个 SQL 查询。

问题描述

我正在编码（或者我应该说重新编码）Wordpress 的插件。

我有这个查询使用 meta.key 打印年、月和总距离 pr 月：'_rl_distance_value'：

SELECT DATE_FORMAT( wp_posts.post_date, '%Y' ) AS Runyear, MONTH( wp_posts.post_date ) AS Runmonth, SUM( wp_postmeta.meta_value ) AS Distance
FROM wp_postmeta
INNER JOIN wp_posts ON ( wp_postmeta.post_id = wp_posts.id )
WHERE wp_postmeta.meta_key = '_rl_distance_value'
AND wp_posts.post_status = 'publish'
AND wp_postmeta.post_id = wp_posts.id
AND year(wp_posts.post_date)= '2010'
GROUP BY DATE_FORMAT( wp_posts.post_date, '%Y-%m' )

它打印如下内容：

Runyear     Runmonth    Distance    
2010    1   76260
2010    2   88670
2010    3   106490
2010    4   57199
2010    5   66460
2010    6   84480
2010    7   54490
2010    8   45090
2010    9   27140
2010    10  70600
2010    11  51000

我也有这个查询来获得每个月使用 meta.key 运行的总时间：'_rl_time_value'

SELECT DATE_FORMAT( wp_posts.post_date, '%Y-%m' ) AS Runyear, MONTH( wp_posts.post_date ) AS Runmonth, ((SUM( time_to_sec( STR_TO_DATE( wp_postmeta.meta_value, '%T' ) ) ) )/3600) AS Runtimetotal
FROM wp_postmeta
INNER JOIN wp_posts ON ( wp_postmeta.post_id = wp_posts.id )
WHERE wp_postmeta.meta_key = '_rl_time_value'
AND wp_posts.post_status = 'publish'
AND year(wp_posts.post_date)= '2010'
GROUP BY DATE_FORMAT( wp_posts.post_date, '%Y-%m' )

它打印如下内容：

Runyear     Runmonth    Runtimetotal    
2010-01     1   7.8928
2010-02     2   9.1667
2010-03     3   10.7083
2010-04     4   5.7497
2010-05     5   6.9561
2010-06     6   8.5594
2010-07     7   5.5767
2010-08     8   4.6139
2010-09     9   2.7233
2010-10     10  7.3953
2010-11     11  4.5858

如何合并这两个查询以获得这样的结构

Runyear     Runmonth    Distance    Runtimetotal

我不知道该怎么做？

你能帮我吗？

标签： mysqlsqlwordpress

解决方案

在下面使用左右连接和联合 sudo 查询

select t1.*,t2.Runtimetotal from your_1stQuery_output as t1
left join your_2ndQuery_output as t2
on t1.Runyear=t2.Runyear and t1.Runmonth=t2.Runmonth
union
select t2.*,t1.Distance from your_1stQuery_output as t1
right join your_2ndQuery_output as t2
on t1.Runyear=t2.Runyear and t1.Runmonth=t2.Runmonth

推荐阅读