这可能吗?我有以下表格
ID CAR MODEL YEAR FUELTYPE
1 Volvo S80 2007 GAS
2 Volvo S80D 2007 DIESEL
3 Volvo S80E 2009 LPN
我想做的是按年计算不同的燃料类型,输出将是这样的,这是我想使用的谷歌图表的格式。
Year GAS DIESEL LPN
2007 1 1 0
2009 0 0 1
如何使用MySQL?我能做这样的事吗?
SELECT YEAR, COUNT(FUELTYPE) AS GAS, COUNT (FUELTYPE) AS DIESEL, COUNT (FUELTYPE) AS LPN
FROM CARDATA
GROUP BY FUELTYPE
ORDER BY YEAR
我知道这是不对的,因为我在每个燃料类型得到相同的计数值-只是把它拉出mysql,然后使用php排序/计数更好吗?
您可以使用带有CASE
表达式的聚合函数将行数据透视到列中:
select year,
sum(case when fueltype = 'gas' then 1 else 0 end) gas,
sum(case when fueltype = 'diesel' then 1 else 0 end) diesel,
sum(case when fueltype = 'lpn' then 1 else 0 end) lpn
from yt
group by year
参见SQL Fiddle with Demo。
如果您有未知数量的FuelType
值,那么您可以使用预处理语句生成动态SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN fueltype = ''',
fueltype,
''' THEN 1 else 0 END) AS `',
fueltype, '`'
)
) INTO @sql
FROM yt;
SET @sql
= CONCAT('SELECT year, ', @sql, '
from yt
group by year');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
参见SQL Fiddle with Demo。这两个结果:
| YEAR | GAS | DIESEL | LPN |
-----------------------------
| 2007 | 1 | 1 | 0 |
| 2009 | 0 | 0 | 1 |
如果只有三个FuelType
,则MySQL
中最简单的解决方法是使用GROUP BY
和SUM()
。
SELECT Year,
SUM(FUELTYPE = 'GAS') GAS,
SUM(FUELTYPE = 'DIESEL') DIESEL,
SUM(FUELTYPE = 'LPN') LPN
FROM CARDATA
GROUP BY YEAR
- <
- SQLFiddle演示/gh>
但是如果你有未知数量的FUELTYPE
,动态sql是更可取的。
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('SUM(FUELTYPE = ''',
FUELTYPE, ''') AS ',
CONCAT('`', FUELTYPE, '`')
)) INTO @sql
FROM CARDATA;
SET @sql = CONCAT('SELECT Year, ', @sql, '
FROM CARDATA
GROUP BY YEAR');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
- <
- SQLFiddle演示/gh>
输出
╔══════╦═════╦════════╦═════╗
║ YEAR ║ GAS ║ DIESEL ║ LPN ║
╠══════╬═════╬════════╬═════╣
║ 2007 ║ 1 ║ 1 ║ 0 ║
║ 2009 ║ 0 ║ 0 ║ 1 ║
╚══════╩═════╩════════╩═════╝