php - 如何在 1 个查询中执行？

Question

我有 2 个表 customer 和 orders:

客户:

| custID | Name  | Age |
|--------|-------|-----|
| 1      | Peter | 23  |
| 2      | Julie | 34  |
| 3      | Tom   | 45  |

订单:

| custID | product | color |
|--------|---------|-------|
| 1      | shirt   | blue  |
| 1      | jacket  | black |
| 2      | jacket  | green |
| 3      | hat     | grey  |
| 3      | shirt   | white |

我现在想要获取所有客户及其订单，并将其作为列表进行排序。所以像这样:

Array
(
    [0] => Array
        (
            [ID] => 1
            [name] => Peter
            [age] => 23
            [orders] => Array
                (
                    [0] => Array
                        (
                            [product] => shirt
                            [color] => blue
                        )

                    [1] => Array
                        (
                            [product] => jacket
                            [color] => black
                        )
                )
        )
    [1] => Array
        (
            [ID] => 2
            [name] => Julie
            [age] => 34
            [orders] => Array
                (
                    [0] => Array
                        (
                            [product] => jacket
                            [color] => green
                        )
                )
        )
    [2] => Array
        (
            [ID] => 3
            [name] => Tom
            [age] => 45
            [orders] => Array
                (
                    [0] => Array
                        (
                            [product] => hat
                            [color] => grey
                        )
                    [1] => Array
                        (
                            [product] => shirt
                            [color] => white
                        )
                )
        )
)

当我这样做时:

SELECT name, age, product, color
FROM `customers`, `orders`
where `customers`.`id` = `orders`.id
group by name

我得到:

| name  | age | product | color |
|-------|-----|---------|-------|
| Peter | 23  | jacket  | green |
| Julie | 34  | shirt   | blue  |
| Tom   | 45  | hat     | grey  |

这甚至可以只用一个查询吗？

Answer 1

您可以简单地进行以下查询:

SELECT *
FROM customers
JOIN orders
USING custID
GROUP BY Name
ORDER BY custID ASC;