mysql - 如何对一年内每月基数表中的值进行分类

Question

嗨，我有三个表格列
Vehicle_id int(11) auto_increment Primary_key,
Vehicle_reg_no varchar(100) not_null,
inp_datetime datetime not_null

我需要提取这些数据。
车辆登记号 | 一月 | 二月 | 三月 ....... 十二月 | 总计

到目前为止，我执行了此查询，但没有得到预期值。

SELECT COUNT(vehicle_master.vehicle_id)as countVal,
vehicle_master.vehicle_reg_no,
MONTHNAME(STR_TO_DATE(EXTRACT(MONTH FROM vehicle_master.inp_datetime),'%m'))as monthName
FROM vehicle_master
GROUP BY  vehicle_master.inp_datetime

Answer 1

我不太确定你的架构，但从我所看到的来看，你可能只是在寻找这个:

SELECT DATE_FORMAT(vm01.inp_datetime,"%M") as monthName
, COUNT(*) as countVal
FROM vehicle_master vm
GROUP BY monthName

Month    | Count
------------------
January  |   10
February |   13
March    |    4
etc      |  etc

但是，我感觉这不是您想要做的，在这种情况下，这可能会给您一个想法(或者至少会给您上面指定的输出):

SELECT vm.vehicle_reg_no as `Vehicle Register Number`
, COUNT(mv01.*) as January
, COUNT(mv02.*) as February
, COUNT(mv03.*) as March
-- , COUNT(mv04.*) as etc
, COUNT(mv.*) as Total
FROM vehicle_master vm
LEFT JOIN vehicle_master vm01 on vm01.vehicle_id=vm.vehicle_id and vm01.vehicle_reg_no=vm.vehicle_reg_no and vm01.inp_datetime=vm.inp_datetime and DATE_FORMAT(vm01.inp_datetime,"%m") = "01"
LEFT JOIN vehicle_master vm02 on vm02.vehicle_id=vm.vehicle_id and vm02.vehicle_reg_no=vm.vehicle_reg_no and vm02.inp_datetime=vm.inp_datetime and DATE_FORMAT(vm02.inp_datetime,"%m") = "02"
LEFT JOIN vehicle_master vm03 on vm03.vehicle_id=vm.vehicle_id and vm03.vehicle_reg_no=vm.vehicle_reg_no and vm03.inp_datetime=vm.inp_datetime and DATE_FORMAT(vm03.inp_datetime,"%m") = "03" -- etc
GROUP BY vehicle_reg_no