MySQL - 需要选择带有WHERE子句的MIN和MAX，作为更大查询的一部分

I have something like this:

我有这样的事情：

SELECT foo1, foo2, foo3, MAX(foo4) AS max FROM table WHERE foo5 = bar;

The thing is, in addition to having the WHERE foo5 = bar clause apply to the whole query, I also want to specify a condition for foo4. I don't just want the maximum value of foo4 from the whole table, but the maximum value of foo4 when another field is equal to something specific. So something like this.

问题是，除了将WHERE foo5 = bar子句应用于整个查询之外，我还想为foo4指定一个条件。我不仅希望整个表中的foo4的最大值，而是另一个字段等于特定内容时foo4的最大值。所以这样的事情。

SELECT foo1, foo2, foo3, MAX(foo4 WHERE field=otherthing) AS max FROM table WHERE foo5 = bar;

How can I do this?

我怎样才能做到这一点？

EDIT: Here is some more detail. This is my current query:

编辑：这里有一些更详细的信息。这是我目前的查询：

SELECT Car_Type, Car_Imm, MIN(Use_Kilom) AS Init_Kilom, 
       MAX(Use_Kilom) AS Final_Kilom, MAX(Use_Kilom) 
       - MIN(Use_Kilom) AS Distance_Kilom, null, null, 
       SUM(Use_Amount) AS Amount_Total, 
       SUM(Use_Liter) / 20 AS Liter_Total, null, null 
FROM fueluse, car 
WHERE Use_Car = Car_Code AND 
       (Use_Date BETWEEN '$datefrom' AND '$dateto')

In this case, MAX(Use_Kilom) and MIN(Use_Kilom) are both getting the absolute maximum and minimum values from the entire table. I just want them to get the maximum value for each Use_Car value.

在这种情况下，MAX（Use_Kilom）和MIN（Use_Kilom）都从整个表中获取绝对最大值和最小值。我只是希望他们获得每个Use_Car值的最大值。

I tried replacing them with the following, respectively:

我尝试分别用以下内容替换它们：

(SELECT MIN(Use_Kilom) FROM fueluse, car WHERE Use_Car = Car_Code) AS Init_Kilom
(SELECT MAX(Use_Kilom) FROM fueluse, car WHERE Use_Car = Car_Code) AS Final_Kilom

But they're still giving me the absolute minimum and maximum values in the entire fueluse table's Use_Kilom field rather than just for each Use_Car's maximum and minimum values of Use_Kilom. Note that Use_Car and Car_Code are linked as a foreign key.

但它们仍然在整个fueluse表的Use_Kilom字段中给出绝对最小值和最大值，而不仅仅是每个Use_Car的Use_Kilom的最大值和最小值。请注意，Use_Car和Car_Code链接为外键。

1 个解决方案

#1

I would expect to see a group by clause then using aggregate functions(min,max,sum etc). In this case probably group by Car_Type, Car_Imm. The implict join should also be changed to an explicit join.

我希望看到一个group by子句然后使用聚合函数（min，max，sum等）。在这种情况下，可能由Car_Type，Car_Imm组。隐含连接也应更改为显式连接。

SELECT Car_Type, Car_Imm, MIN(Use_Kilom) AS Init_Kilom, 
       MAX(Use_Kilom) AS Final_Kilom, MAX(Use_Kilom) 
       - MIN(Use_Kilom) AS Distance_Kilom, null, null, 
       SUM(Use_Amount) AS Amount_Total, 
       SUM(Use_Liter) / 20 AS Liter_Total, null, null 
FROM fueluse
join  car on Use_Car = Car_Code AND 
where (Use_Date BETWEEN '$datefrom' AND '$dateto')
group by Car_Type, Car_Imm

It would make things clearer if you qualified your fieldnames with the table names they belong to (or their alias)

如果您使用他们所属的表名（或他们的别名）限定您的字段名，这将使事情变得更清楚

#1