[MYSQL QUERY / LARAVEL ELOQUENT QUESTION]
您好,我真的需要一些帮助MYSQL查询这3个表,我有.订单表和详细信息是在我们的系统上手动输入的.
我是MYSQL新手,需要帮助而不是法官.
PERSON TABLE人
ID | PERSON_NAME |
---|---|
1 | Jackie |
2 | Chan |
ORDER TABLE(订单)
ID | PERSON_ID | ORDER_DATE |
---|---|---|
1 | 1 (Jackie) | January 01, 2022 |
2 | 1 (Jackie) | January 02, 2022 |
3 | 2 (Chan) | January 02, 2022 |
4 | 2 (Chan) | January 03, 2022 |
5 | 1 (Jackie) | January 04, 2022 |
6 | 1 (Jackie) | January 05, 2022 |
ORDER DETAIL TABLE(订单详情)
ID | ORDER_ID | DESCRIPTION | PRICE |
---|---|---|---|
1 | 1 | Apple | 100 |
2 | 1 | Orange | 80 |
3 | 1 | Mango | 90 |
4 | 3 | Grape | 50 |
5 | 3 | Apple | 100 |
6 | 4 | Apple | 100 |
7 | 5 | Orange | 80 |
8 | 5 | Mango | 90 |
QUESTION
在这里,我将三个表合并在一起,这就是我得到的,它显示Order ID 2 and 6没有在我们的系统上添加任何订单细节(手动),比如说我忘记添加细节.所以我需要在个人页面上显示为一个错误.
SELECT
o.id as order_id,
p.name as person_name,
o.date as order_date,
COUNT(od.id) as count_order_detail,
SUM(od.price) as subtotal
FROM persons p
JOIN orders o ON o.person_id = p.id
JOIN order_details od ON od.order_id = o.id
GROUP BY o.order_id
ORDER_ID | PERSON_NAME | ORDER_DATE | COUNT_ORDER_DETAIL | SUBTOTAL |
---|---|---|---|---|
1 | Jackie | January 01, 2022 | 3 | 270 |
2 | Jackie | January 02, 2022 | 0 | 0 |
3 | Chan | January 02, 2022 | 2 | 150 |
4 | Chan | January 03, 2022 | 1 | 100 |
5 | Jackie | January 04, 2022 | 2 | 170 |
6 | Jackie | January 05, 2022 | 0 | 0 |
EXPECTED QUERY RESULT ON MYSQL TABLE
这是我期望的结果,显示了订单的结果,没有任何细节.
PERSON_NAME | ORDERS | ERROR_ORDER |
---|---|---|
Jackie | 4 Orders | 2 |
Chan | 2 Orders | - |
我该怎么做?我试着数数,但数错了.
100