mysql连接同一张表不同的结果集 [英] mysql join same table different result set

问题描述

我想将来自同一张表的不同结果合并为一个大结果.

I would like to combine different results from the same table as one big result.

SELECT host_name,stats_avgcpu,stats_avgmem,stats_avgswap,stats_avgiowait 
    FROM sar_stats,sar_hosts,sar_appgroups,sar_environments
    WHERE stats_host = host_id
    AND host_environment = env_id
    AND env_name = 'Staging 2'
    AND host_appgroup = group_id
    AND group_name = 'Pervasive'
    AND DATE(stats_report_time) =  DATE_SUB(curdate(), INTERVAL 1 DAY)

SELECT AVG(stats_avgcpu),AVG(stats_avgmem),AVG(stats_avgswap),AVG(stats_avgiowait)
 FROM sar_stats 
 WHERE  stats_id = "stat_id of the first query" and DATE(stats_report_time) 
   BETWEEN DATE_SUB(curdate(), INTERVAL 8 DAY) and DATE_SUB(curdate(), INTERVAL 1 DAY)

SELECT AVG(stats_avgcpu),AVG(stats_avgmem),AVG(stats_avgswap),AVG(stats_avgiowait)
 FROM sar_stats 
 WHERE  stats_id = "stat_id of the first query" and DATE(stats_report_time) 
   BETWEEN DATE_SUB(curdate(), INTERVAL 31 DAY) and DATE_SUB(curdate(), INTERVAL 1 DAY)

所需的输出将类似于...

Desired output would be something like ...

host_name|stats_avgcpu|stats_avgmem|stats_avgswap|stats_avgiowait|7daycpuavg|7daymemavg|7dayswapavg|7dayiowaitavg|30daycpuavg|30daymemavg|....etc

SQL小提琴 http://sqlfiddle.com/#!8/4930b/3

推荐答案

看来这就是您想要的.我更新了第一个查询，以使用正确的ANSI JOIN语法，然后对于其他两个查询，通过stats_host字段上的LEFT JOIN将它们连接在一起:

It seems like this is what you want. I updated the first query to use proper ANSI JOIN syntax and then for the additional two queries they were joined via a LEFT JOIN on the stats_host field:

SELECT s.stats_host,
  h.host_name,
  s.stats_avgcpu,
  s.stats_avgmem,
  s.stats_avgswap,
  s.stats_avgiowait,
  s7.7dayavgcpu,
  s7.7dayavgmem,
  s7.7dayavgswap,
  s7.7dayavgiowait,
  s30.30dayavgcpu,
  s30.30dayavgmem,
  s30.30dayavgswap,
  s30.30dayavgiowait
FROM sar_stats s
INNER JOIN sar_hosts h
  on s.stats_host = h.host_id
INNER JOIN sar_appgroups a
  on h.host_appgroup = a.group_id
  and a.group_name = 'Pervasive'
INNER JOIN sar_environments e
  on h.host_environment = e.env_id
  and e.env_name = 'Staging 2'
LEFT JOIN
(
  SELECT s.stats_host,
    AVG(s.stats_avgcpu) AS '7dayavgcpu',
    AVG(s.stats_avgmem) AS '7dayavgmem',
    AVG(s.stats_avgswap) AS '7dayavgswap',
    AVG(s.stats_avgiowait) AS '7dayavgiowait'
  FROM sar_stats s
  WHERE DATE(stats_report_time) BETWEEN DATE_SUB(curdate(), INTERVAL 8 DAY) AND DATE_SUB(curdate(), INTERVAL 1 DAY)
  GROUP BY s.stats_host
) s7
  on s.stats_host = s7.stats_host
LEFT JOIN
(
  SELECT s.stats_host,
    AVG(s.stats_avgcpu) AS '30dayavgcpu',
    AVG(s.stats_avgmem) AS '30dayavgmem',
    AVG(s.stats_avgswap) AS '30dayavgswap',
    AVG(s.stats_avgiowait) AS '30dayavgiowait'
  FROM sar_stats s
  WHERE DATE(s.stats_report_time) BETWEEN DATE_SUB(curdate(), INTERVAL 31 DAY) AND DATE_SUB(curdate(), INTERVAL 1 DAY)
  GROUP BY s.stats_host
) s30
  on s.stats_host = s30.stats_host
WHERE DATE(s.stats_report_time) =  DATE_SUB(curdate(), INTERVAL 1 DAY);

这篇关于mysql连接同一张表不同的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！