如何用子查询在单列中显示多个结果

Question

我有一个订单表，其中显示有关订单号、产品和客户的信息。

我需要在products列中显示产品名称及其数量列表分隔或在新行中，如下表所示:

预期结果

id    |   order_number   |  customer    |  address      |    total    |  products
------------------------------------------------------------------------------------
1     |    OR00123       |  Tom Helks   |  Test 221     |    1233,2   | 1x iphone
| 2x samsung
|  3x someproduct

<<p>订单表/strong>

id    |   order_number   |  customer_id  |    total    |  status_id
------------------------------------------------------------------------------------
1     |    OR00123       |    1          |   1233,2    | 1

<<p>OrderProducts表/strong>

id    |   order_id   |  product_id    |  qty      |    
------------------------------------------------------
1    |    1         |  5             |  1        |
2    |    1         |  2             |  2        |
3    |    1         |  6             |  3        |

产品表

id    |   name            |  price  
----------------------------------------
5    |    iphone         |  1231            
2    |    samsung        |  2322             
6    |    someproduct    |  432   

What i try.

我得到以上预期的结果，但只是不做子查询列出的产品和数量的订单。

SELECT  
order.number,
customer.name,
customer.adress,
SUM(order_products.qty * product.price) as total,

# subquery here
(
SELECT p.name FROM products p WHERE order_products.product_id = p.id
) as products

FROM.orders as order
INNER JOIN customer on customer.customer_id= customer.id
INNER JOIN order_products on order.id = order_products.order_id
ORDER BY dok.created_at;

我的想象力停止了，我相信问题在子查询中，但我暂时看不到它。

感谢

Answer 1

您需要连接所有表并按唯一值分组。

GROUP_CONCAT必须与所需值的CONCAT一起使用

CREATE TABLE orders
(`id` int, `order_number` varchar(7), `customer_id` int, `total` varchar(6), `status_id` int)
;

INSERT INTO orders
(`id`, `order_number`, `customer_id`, `total`, `status_id`)
VALUES
(1, 'OR00123', 1, '1233,2', 1)
;

CREATE TABLE orders_product
(`id` int, `order_id` int, `product_id` int, `qty` int)
;

INSERT INTO orders_product
(`id`, `order_id`, `product_id`, `qty`)
VALUES
(1, 1, 5, 1),
(2, 1, 2, 2),
(3, 1, 6, 3)
;)

Records: 3  Duplicates: 0  Warnings: 0

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

CREATE TABLE product
(`id` int, `name` varchar(11), `price` int)
;

INSERT INTO product
(`id`, `name`, `price`)
VALUES
(5, 'iphone', 1231),
(2, 'samsung', 2322),
(6, 'someproduct', 432)
;

Records: 3  Duplicates: 0  Warnings: 0

SELECT 
order_number, `customer_id`, `total`,GROUP_CONCAT(CONCAT( op.`qty`,'x ',p.`name`) SEPARATOR  ' ') products
FROM orders o
JOIN orders_product op ON o.`id` = op.`order_id`
JOIN product p ON op.`product_id` = p.`id`
GROUP BY order_number, `customer_id`, `total`

1x iPhone 2x三星3x某某产品