数据结构图
期望结构图
实现代码
SELECT
t.years,
t.manufacturer,
t.object_name,
t.year_count,
@running_total:=@running_total + t.year_count AS cum_year_count
FROM
(SELECT
SUBSTRING_INDEX(plant_transfer_date, '-', 1) AS years,
manufacturer,
object_name,
COUNT(1) AS year_count
FROM
scxt_dm_rp_main_device
WHERE
manufacturer = 'A'
GROUP BY object_name , manufacturer , years) t
JOIN
(SELECT @running_total:=0) r
ORDER BY t.years;