如何对两个表使用max

Question

我想在时间字段上使用MAX，它可以工作，但是值是"0“

这个查询是在我使用MAX之前

SELECT a.`JAM`, AVG(a.PacketLoss) AVG
FROM (
SELECT `JAM`,`RNC`,`IPPATH_PM` AS PHB,`VS_IPPM_FORWORD_DROPMEANS` AS PacketLoss, `VSIPPMRttMeans` AS Latency, 'IPPM'
FROM `rnc_ippm_meas`
WHERE `JAM`>= CURDATE() AND `IPPATH_PM` LIKE '%AF31%'
UNION
SELECT `JAM`,`RNC`,`IPPOOL_PM`,`VSIPPOOLIPPMForwardDrop-Means`,`VSIPPOOLIPPMRttMeans`, 'IPPOOLPM'
FROM `rnc_ippool_ippm_meas` 
WHERE `JAM`>= CURDATE() AND `IPPOOL_PM` LIKE '%AF31%') a
GROUP BY a.`JAM`
LIMIT 10000 

这里的输出

output1

然后我在这里使用MAX

SELECT a.`JAM`, AVG(a.PacketLoss) AVG
FROM (
SELECT MAX(JAM) AS JAM,`RNC`,`IPPATH_PM` AS PHB,`VS_IPPM_FORWORD_DROPMEANS` AS PacketLoss, `VSIPPMRttMeans` AS Latency, 'IPPM'
FROM `rnc_ippm_meas`
WHERE `JAM`>= CURDATE() AND `IPPATH_PM` LIKE '%AF31%'
UNION
SELECT MAX(JAM) AS JAM,`RNC`,`IPPOOL_PM`,`VSIPPOOLIPPMForwardDrop-Means`,`VSIPPOOLIPPMRttMeans`, 'IPPOOLPM'
FROM `rnc_ippool_ippm_meas` 
WHERE `JAM`>= CURDATE() AND `IPPOOL_PM` LIKE '%AF31%') a
GROUP BY a.`JAM`
LIMIT 10000

output2

我预计输出是最后一次的值，有人能帮我吗？谢谢

Answer 1

您需要将MAX放在外部查询上，而不是内部查询上。在执行内部查询时，MySQL会给出一个非聚合列的不确定值，例如VS_IPPM_FORWORD_DROPMEANS，对于您的样本数据，它似乎是一个0值。将您的查询更改为：

SELECT MAX(a.`JAM`), AVG(a.PacketLoss) AVG
FROM (
SELECT `JAM`,`RNC`,`IPPATH_PM` AS PHB,`VS_IPPM_FORWORD_DROPMEANS` AS PacketLoss, `VSIPPMRttMeans` AS Latency, 'IPPM'
FROM `rnc_ippm_meas`
WHERE `JAM`>= CURDATE() AND `IPPATH_PM` LIKE '%AF31%'
UNION
SELECT `JAM`,`RNC`,`IPPOOL_PM`,`VSIPPOOLIPPMForwardDrop-Means`,`VSIPPOOLIPPMRttMeans`, 'IPPOOLPM'
FROM `rnc_ippool_ippm_meas` 
WHERE `JAM`>= CURDATE() AND `IPPOOL_PM` LIKE '%AF31%') a

注意:不再需要GROUP BY，因为您将对整个数据集使用MAX和AVG，并且不需要LIMIT，因为此查询将只生成1行。