mysql - 多个选择语句在mysql中给出错误的输出

Question

我正在尝试执行一个多选语句查询，如下所示，

select Date(Transactiondate),
(Select count(*) from tms_plaza.tms_lanetrans where vehiclecatcode='cat1' and transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01') as LMV,
(Select count(*)  from tms_plaza.tms_lanetrans where vehiclecatcode='cat2' and transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01' ) as LCV,
(Select count(*)  from tms_plaza.tms_lanetrans where vehiclecatcode='cat3' and transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01' ) as Truck,
(Select count(*)  from tms_plaza.tms_lanetrans where vehiclecatcode='cat4' and transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01' ) as Bus,
(Select count(*)  from tms_plaza.tms_lanetrans where vehiclecatcode='cat5' and transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01' ) as MAV,
(Select count(*)  from tms_plaza.tms_lanetrans where vehiclecatcode='cat6' and transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01' ) as MAV6
from tms_plaza.tms_lanetrans where transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01' group by date(transactiondate);

但这给出了非常错误的输出，因为对于所有日期它正在计算相同的数字，如下所示

2018-05-17  292 628 1317    165 1423    9
2018-05-17  292 628 1317    165 1423    9
2018-05-17  292 628 1317    165 1423    9
2018-05-17  292 628 1317    165 1423    9
2018-05-17  292 628 1317    165 1423    9
2018-05-17  292 628 1317    165 1423    9
2018-05-17  292 628 1317    165 1423    9
2018-05-17  292 628 1317    165 1423    9

有人可以帮我解决我在这方面做错的地方吗？

标签： mysql

Answer 1

您可以使用条件聚合来做到这一点：

select Date(Transactiondate),
  sum(vehiclecatcode='cat1') as LMV,
  sum(vehiclecatcode='cat2') as LCV,
  sum(vehiclecatcode='cat3') as Truck,
  sum(vehiclecatcode='cat4') as Bus,
  sum(vehiclecatcode='cat5') as MAV,
  sum(vehiclecatcode='cat6') as MAV6
from tms_plaza.tms_lanetrans 
where transactiondate >= '2018-05-01' and transactiondate <= '2018-07-01' 
group by date(transactiondate);