mysql - 通过分组优化 SQL 查询

Question

我有一个具有以下结构的 mySQL 表

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| make        | varchar(55)      | NO   | MUL | NULL    |                |
| model       | varchar(55)      | NO   | MUL | NULL    |                |
| year        | varchar(4)       | NO   | MUL | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

和一些示例数据

('Chevrolet', 'Express', '2001'),
('Chevrolet', 'Express', '2002'),
('Chevrolet', 'Venture', '2001'),
('Chevrolet', 'Venture', '2002'),
('Dodge', 'Dakota', '2000'),
('Dodge', 'Dakota', '2001'),
('Ford', 'Flex', '2009'),
('Ford', 'Flex', '2010'),

我想要的是一个快速查询，它可以返回所有型号，并附带所有品牌和年份。因此，以下是我希望返回的示例。

Array
(
    [Chevrolet] => Array
        (
            [Express] => Array
                (
                    [0] => 2001
                    [1] => 2002
                )

            [Venture] => Array
                (
                    [0] => 2001
                    [1] => 2002
                )
        )

    [Dodge] => Array
        (
            [Dakota] => Array
                (
                    [0] => 2001
                    [1] => 2002
                )
        )

    [Ford] => Array
        (
            [Flex] => Array
                (
                    [0] => 2009
                    [1] => 2010
                )
        )
)

我目前在做什么:

我有一个查询返回所有独特品牌:

$sql = "select distinct make from listings order by make asc ";

一旦有了每个 make，我就会循环并使用 make 运行以下查询:

$sql = "select distinct model from listings where make = ? order by model asc ";

我再走一步捕获岁月:

$sql = "select distinct year from listings where make=? and model=? order by year desc";

Answer 1

SELECT make, model, year
FROM listings
ORDER BY make, model, year