一个表DATA_TABLE
在同一列中有两个不同的数值数据-Engine_Technical
该表有一个单独的列-Engine_Technical_Type
-用于标识类型的数据Engine_Technical
,即"CO2排放"或《MPG》
该表还有一个列-TECH_Id
-不是唯一的,是特定于车辆的。
我希望能够生成一个查询,过滤与两个单独的输入条件值不匹配的行。
例如:
我想找到MPG小于25和排放=的车辆,比如17
当我筛选的数据在同一列中时,我只是不知道如何返回同时具有这两个数据的行。
两个单独的查询工作
/*对于MPG*/
SELECT * FROM `DATA_TABLE`
WHERE `TECH_Id` = 111
AND `Engine_Technical_Type` = "MPG"
AND `Engine_Technical` < 25
/*对于CO2*/
SELECT * FROM `DATA_TABLE`
WHERE `TECH_Id` = 111
AND `Engine_Technical_Type` = "CO2 EMISSIONS"
AND `Engine_Technical` = 17
|DATA_TABLE|
| TECH_Id | Engine_Technical_Type | Engine_Technical |
------------------------------------------------------
| 111 | CO2 EMISSIONS | 15 |
------------------------------------------------------
| 111 | CO2 EMISSIONS | 17 |
------------------------------------------------------
| 111 | MPG | 15 |
------------------------------------------------------
| 111 | CO2 EMISSIONS | 15 |
------------------------------------------------------
| 111 | MPG | 35 |
------------------------------------------------------
| 111 | CO2 EMISSIONS | 15 |
------------------------------------------------------
| 999 | MPG | 10 |
------------------------------------------------------
| 999 | CO2 EMISSIONS | 17 |
------------------------------------------------------
期望结果:
|VEHICLE|
|ID|| TECH_Id | CO2 EMISSIONS | MPG |
----------------------------------------------------------
1 | 111 | 17 | 15 |
----------------------------------------------------------
我只需要在一个查询中完成这项工作,并将MPG和CO2排放量放在一个单独的列中。如有任何帮助,我们将不胜感激。非常感谢。
考虑使用case
:
select id, tech_id
, sum(case engine_technical_type
when 'CO2 EMISSIONS' then engine_technical
end) as co2_emissions
, sum(case engine_technical_type
when 'MPG' then engine_technical
end) as mpg
from data_table
group by id
SELECT tech_id,
MAX(CASE WHEN Engine_Technical_Type = 'MPG' THEN Engine_Technical END) as mpg,
MAX(CASE WHEN Engine_Technical_Type = 'CO2 EMISSIONS' THEN Engine_Technical END) as co2_emissions
FROM `DATA_TABLE`
WHERE `TECH_Id` = 111 AND
((Engine_Technical_Type = 'MPG' AND Engine_Technical < 25) OR
(Engine_Technical_Type` = 'CO2 EMISSIONS' AND `Engine_Technical` = 17)
)
GROUP BY tech_id;
我不确定id
应该是什么。