的实际最大值。
考虑以下数据库结构:
__________ __________
| Trucks | | Mileage |
|__________|__________ |__________|________________________
| ID | DRIVER | | TRUCK_ID | MILEAGE | OIL_CHANGE |
|---------------------| |-----------------------------------|
| 1 | Tony | | 1 | 100000 105000 |
| 2 | George | | 2 | 6020 10020 |
| 3 | Mary | | 3 | 37798 41000 |
|_____________________| | 3 | 41233 47200 |
| 3 | 49000 |
|___________________________________|
我希望得到一个结果集,其中包含每个驾驶员的最大英里数和最大换油次数。
_________________________________
| 1 | Tony | 100000 | 105000 |
| 2 | George| 6020 | 10020 |
| 3 | Mary | 49000 | 47200 |
|_______________________________|
这是我到目前为止所尝试的:
SELECT t.*, MAX(m.mileage) AS mileage, MAX(m.oil_change) AS oil_change
FROM trucks t
LEFT JOIN mileage m ON t.id = m.truck_id
GROUP BY t.id
但这似乎不允许MAX
函数正常工作。它并不总是包含mileage
明白了!您的里程列必须定义为字符类型,而不是数字类型!当发生这种情况时,按字母顺序排序,而不是按值排序。
您应该将mileage
和oil_change
列转换为数字类型(根据提供的数据样本,我建议使用INT
)。
当你不转换它们时,这将工作:
SELECT t.*, MAX(cast(m.mileage as int)) AS mileage,
MAX(cast(m.oil_change as int)) AS oil_change
FROM trucks t
LEFT JOIN mileage m ON t.id = m.truck_id
GROUP BY t.id
下面的查询应该可以解决您的问题。
SELECT T.DRIVER,MIN(MILEAGE) AS MIN_MILEAGE,MIN(OIL_CHANGE) AS MIN_OIL_CHANGE
FROM TRUCKS T INNER JOIN MILEAGE M
ON T.ID = M.TRUCK_ID
GROUP BY T.DRIVER;
SELECT T.DRIVER,MAX(MILEAGE) AS MAX_MILEAGE,MAX(OIL_CHANGE) AS MAX_OIL_CHANGE
FROM TRUCKS T INNER JOIN MILEAGE M
ON T.ID = M.TRUCK_ID
GROUP BY T.DRIVER;
的问候Venk