mysql - 如何获得总和(具体)

Question

Fe_Order_Items

fe_order_items_id
fe_order_specification_id
fe_users_id
fe_menu_items_id
fe_order_items_quantity
fe_order_items_time_order

Fe_Order_Specification

fe_order_specification_id
fe_users_id
fe_users_address_id
fe_users_email_address_id
fe_users_phone_number_id
fe_menu_items_id
fe_menu_items_quantity
fe_payment_customer_payment_order_total
fe_payment_customer_payment_acceptance
fe_payment_customer_payment_acceptance_by_food_express
fe_payment_customer_method_of_payment
fe_customer_service_order_process_start
fe_delivery_system_order_signed_by_customer_before_delivery
fe_delivery_system_order_order_delivered_to_customer
total

我运行的查询(我的导师帮助我完成了它，因为仍然很难想出一些东西来计算总数(所有已订购的插入菜单项):

SELECT fe_order_specification.fe_order_specification_id, 
    fe_menu_items.fe_menu_items_name, 
    SUM(fe_menu_items.price `fe_menu_items_quantity`) AS Total 
FROM fe_order_items
INNER JOIN fe_menu_items          ON fe_order_items.fe_menu_items = fe_menu_items.fe_menu_items_id
INNER JOIN fe_order_specification ON fe_order_items.`fe_order_specification` = fe_order_specification.`fe_order_specification_id`
WHERE fe_order_items.fe_order_specification=1
GROUP BY fe_order_items.fe_menu_items

mysql并没有得到一个不错的总和，而是给了我一些法语(和英语((我的设置是英国英语，一切都是英语)):

1054 - Champ 'fe_order_specification.fe_order_specification_id' inconnu dans field list

Answer 1

我希望查询如下所示:

SELECT SUM(mi.price * oi.quantity)
FROM order_items AS oi
JOIN menu_items AS mi ON mi.id = oi.menu_item_id
WHERE oi.order_id = @ORDERID

但是，恐怕这与您当前的数据模型不匹配...我会更改...

• 订单项与订单有很多关系
• 每个订单项都有一个菜单项，而不涉及多个菜单项。这是使查询您所提供的表格变得非常困难的关键区域，但如果您更改为订单项有一个菜单项，则查询会更容易

数据模型看起来像这样(注意我已经删除了对查询不重要的字段):

订单商品:

id (PK) (AUTO INCREMENTED) (INT)
order_id (FK) (INT)
menu_item_id (FK) (INT)
quantity (INT)

菜单项:

id (PK) (AUTO INCREMENTED) (INT)
price (DECIMAL)

其他说明:

• 我会将 user_id 存储在 order 表(尚未定义)
• 我会将菜单项的类型存储在菜单项上:type (ENUM) ['dinner','dessert']
• 如果需要控制这些项目的数量，应以编程方式完成