車輛分配歷史
id companyAccountId date totalVan totalBike
1 4 2021-11-11 00:00:00 2 0
2 4 2021-11-11 00:00:00 3 0
3 4 2021-11-11 00:00:00 1 0
4 8 2021-11-11 00:00:00 1 0
5 8 2021-11-12 00:00:00 2 0
6 9 2021-11-13 00:00:00 0 2
7 9 2021-11-14 00:00:00 0 1
我想計算每組最后一行的companyAccountId總和。也是一個范圍之間的日期。
例如：-
2021-11-11 -> 2021-11-13
totalVan totalBike
1 2 0 = 3 0 0 2 = 2
2021-11-11 -> 2021-11-14
totalVan totalBike
1 2 0 = 3 0 0 1 = 1
uj5u.com熱心網友回復：
一種方法是取一個復雜字串的最大值（對于每個 companyAccountId），該字串連接 id 和您要查找的最高 id 的欄位，然后從末尾提取您想要的欄位并將其轉換回數字（全部在子查詢中，因此您可以對所有結果值求和）
select sum(latestTotalVan) as totalVan, sum(latestTotalBike) as totalBike
from (
select
cast(substring(max(concat(lpad(id,11,'0'),totalVan)) from 12) as unsigned) latestTotalVan,
cast(substring(max(concat(lpad(id,11,'0'),totalBike)) from 12) as unsigned) latestTotalBike
from vehicle_assignment_history
where date between '2021-11-11 00:00:00' and '2021-11-14 00:00:00'
group by companyAccountId
) latest_values
小提琴
mysql 8 添加了視窗函式，使這種事情變得更加容易。
uj5u.com熱心網友回復：
SELECT companyAccountId, sum(totalVan) AS [Total Vans], sum(totalBike) AS [Total Bike], FROM Vehicle_assignment_history GROUP BY companyAccountId HAVING '2021-11-11' < date AND date < '2021-11-13'
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/355977.html