MySQL join 三张表，item sum of join 到主表

Question

我有三个 MySQL 表:

例如

• 一个表是菜单ID、名称

• B表是customer_order ID，order_date

• C表为order_item ID、menu_item_id、customer_order_id、order_quantity

我试着在这个月输出name, sum(order_quantity)

目前我有两个单独的查询，工作正常，但第二个查询在 foreach 循环内，这看起来不太好。

第一个输出所有菜单项的查询:

$results = $wpdb->get_results( "SELECT * FROM menu WHERE post_id = $pid ORDER BY sort_order ");  

第二个查询将输出每个月售出的每件商品的总额:

$total = $wpdb->get_col( "SELECT SUM(oi.order_item_quantity) 
                          from order_item as oi 
                          INNER JOIN customer_order as ho ON ho.ID = oi.order_id  
                          WHERE oi.order_item_id =  $subC->ID AND YEAR(ho.order_date) = $current_year AND MONTH(ho.order_date) = $current_month ");  

我尝试将两个查询合并为一个查询，这花了我一整天的时间，但仍然无法解决，请谁能帮帮我。

更新

谢谢勒内。

Select m.name, m.name as name, sum(oi.order_item_quantity) as sold_monthly from menu as m left join order_item as oi on oi.order_item_id = m.ID left join cusomter_order as co on co.ID = oi.order_id where m.post_id = 110  group by m.ID, m.name

这将输出

 name                   sold_monthly    
Sushi Lunch Special     NULL
Sushi Lunch             19
Sashimi Lunch           61
jason                   NULL
egg roll                NULL

如果我添加 YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9

我只得到

 name                   sold_monthly    
Sushi Lunch             7
Sashimi Lunch           14

当我添加 YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9 时，我如何让寿司午餐、jason、蛋卷、空项目保持特别。

我来试试

(year(co.order_date) = 2016 and month(co.order_date) = 10) or sold_monthly is null 

这给我一个查询错误

更新再次感谢 Rene

现在可以了

 (year(co.order_date) = 2016 and month(co.order_date) = 10) or co.order_date is null 

终于解决了，上面有小bug，当我改变business_id时可能抓不到我想要的结果，所以我给它加了一个子查询。

Select m.*, p.sold_monthly from menu as m left join ( SELECT SUM(oi.order_item_quantity) as sold_monthly, oi.order_item_id as ID, oi.order_item_name from order_item as oi LEFT JOIN cusomter_order as ho ON ho.ID = oi.order_id WHERE ho.business_id = $pid AND (year(ho.order_date) = $current_year and month(ho.order_date) = $current_month) OR ho.order_date is NULL GROUP by oi.order_item_id )p on p.ID = m.ID where m.post_id = $pid

Answer 1

因此，您正在尝试获取受所选月份限制的每个 post_id 的列表。以下查询将为以下示例数据生成该查询。

SELECT m.ID as ID, m.Name as Name, SUM(oi.order_quantity) as Quantity
FROM menu as m
LEFT JOIN order_item as oi ON oi.menu_item_id = m.ID 
LEFT JOIN customer_order as co ON co.ID = oi.customer_order_id
WHERE m.post_id = 0 AND YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9 OR co.order_date is NULL
GROUP BY m.ID,m.Name,m.sort_order
ORDER BY m.sort_order

示例数据

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `customer_order`;
CREATE TABLE `customer_order` (
  `ID` int(11) NOT NULL,
  `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;

TRUNCATE TABLE `customer_order`;
INSERT INTO `customer_order` (`ID`, `order_date`) VALUES
(1, '2016-09-06 00:00:00'),
(2, '2016-09-13 00:00:00'),
(3, '2016-08-09 00:00:00'),
(4, '2016-09-19 00:00:00');

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
  `ID` int(11) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `Name` varchar(20) COLLATE utf8_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;

TRUNCATE TABLE `menu`;
INSERT INTO `menu` (`ID`, `sort_order`, `post_id`, `Name`) VALUES
(2, 0, 0, 'Test 1'),
(4, 1, 0, 'Test 2'),
(5, 2, 0, 'Test 3');

DROP TABLE IF EXISTS `order_item`;
CREATE TABLE `order_item` (
  `ID` int(11) NOT NULL,
  `menu_item_id` int(11) NOT NULL,
  `customer_order_id` int(11) NOT NULL,
  `order_quantity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;

TRUNCATE TABLE `order_item`;
INSERT INTO `order_item` (`ID`, `menu_item_id`, `customer_order_id`, `order_quantity`) VALUES
(1, 2, 1, 1),
(2, 2, 2, 3),
(3, 4, 1, 1),
(4, 4, 2, 4),
(5, 2, 3, 3),
(6, 4, 3, 1),
(7, 2, 4, 4);


ALTER TABLE `customer_order`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `menu`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `idx_pid` (`post_id`);

ALTER TABLE `order_item`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `idx_coid` (`customer_order_id`),
  ADD KEY `idx_miid` (`menu_item_id`);


ALTER TABLE `customer_order`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE `menu`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
ALTER TABLE `order_item`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `order_item`
  ADD CONSTRAINT `CostomerOrderConstrain` FOREIGN KEY (`customer_order_id`) REFERENCES `customer_order` (`ID`),
  ADD CONSTRAINT `MenuItemConstrain` FOREIGN KEY (`menu_item_id`) REFERENCES `menu` (`ID`);

祝你集成查询好运，让我知道它是否有效。

更新:更新示例数据以重现实际问题。更新了解决方案查询。