mysql 内连接 3 个表的订单计数问题

Question

我有以下示例表。

CREATE TABLE IF NOT EXISTS `my_customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_email` text NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=64 ;

INSERT INTO `my_customer` (`customer_id`, `customer_email`) VALUES
(4, 'muthu.d@test.in'),
(5, 'nsrirengan@test.in'),
(6, 'vinothini.k@test.in'),
(8, 'vinothini.k111@test.in'),
(63, 'sri.n321@test.in'),
(56, 'vesri.n@test.in'),
(57, 'veesri.n@test.in'),
(58, 'veeisri.n@test.in'),
(59, 'ren@test.in'),
(60, 'ren1@test.in'),
(61, 'nsrirengan123@test.in'),
(62, 'nsrirengan321@test.in'),
(53, 'sri.n@test.in'),
(54, 'royalrenga@test.in'),
(55, 'vesri@test.in');

CREATE TABLE IF NOT EXISTS `my_order` (
  `orderid` int(11) NOT NULL AUTO_INCREMENT,
  `ordergenerateid` varchar(20) NOT NULL,
  `restaurant_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `usertype` varchar(10) NOT NULL,
  `customeremail` varchar(200) NOT NULL,
  PRIMARY KEY (`orderid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=152 ;


INSERT INTO `my_order` (`orderid`, `ordergenerateid`, `restaurant_id`, `customer_id`, `usertype`, `customeremail`) VALUES
(1, 'ORD0001', 3, 6, 'C', 'vinothini.k@test.in'),
(2, 'ORD0002', 1, 6, 'C', 'vinothini.k@test.in'),
(3, 'ORD0003', 3, 6, 'C', 'vinothini.k@test.in'),
(4, 'ORD0004', 3, 6, 'C', 'vinothini.k@test.in'),
(5, 'ORD0005', 3, 0, 'G', 'vinothini.k5555555@test.in'),
(6, 'ORD0006', 3, 6, 'C', 'vinothini.k@test.in'),
(7, 'ORD0007', 3, 6, 'C', 'vinothini.k@test.in'),
(8, 'ORD0008', 3, 6, 'C', 'vinothini.k@test.in'),
(9, 'ORD0009', 3, 6, 'C', 'vinothini.k@test.in'),
(10, 'ORD0010', 3, 6, 'C', 'vinothini.k@test.in'),
(11, 'ORD0011', 3, 6, 'C', 'vinothini.k@test.in'),
(12, 'ORD0012', 3, 6, 'C', 'vinothini.k@test.in'),
(13, 'ORD0013', 3, 6, 'C', 'vinothini.k@test.in'),
(14, 'ORD0014', 3, 6, 'C', 'vinothini.k@test.in'),
(15, 'ORD0015', 2, 3, 'C', 'sri.n@test.in'),
(16, 'ORD0016', 2, 3, 'C', 'sri.n@test.in'),
(17, 'ORD0017', 2, 3, 'C', 'sri.n@test.in'),
(18, 'ORD0018', 2, 3, 'C', 'sri.n@test.in'),
(19, 'ORD0019', 2, 3, 'C', 'sri.n@test.in'),
(20, 'ORD0020', 8, 3, 'C', 'sri.n@test.in'),
(21, 'ORD0021', 5, 3, 'C', 'sri.n@test.in'),
(22, 'ORD0022', 13, 3, 'C', 'sri.n@test.in'),
(23, 'ORD0023', 13, 3, 'C', 'sri.n@test.in'),
(24, 'ORD0024', 13, 3, 'C', 'sri.n@test.in'),
(25, 'ORD0025', 13, 3, 'C', 'sri.n@test.in'),
(26, 'ORD0026', 13, 3, 'C', 'sri.n@test.in'),
(27, 'ORD0027', 13, 3, 'C', 'sri.n@test.in'),
(28, 'ORD0028', 13, 3, 'C', 'sri.n@test.in'),
(29, 'ORD0029', 13, 3, 'C', 'sri.n@test.in'),
(30, 'ORD0030', 13, 3, 'C', 'sri.n@test.in'),
(31, 'ORD0031', 13, 3, 'C', 'sri.n@test.in'),
(32, 'ORD0032', 13, 3, 'C', 'sri.n@test.in'),
(33, 'ORD0033', 13, 3, 'C', 'sri.n@test.in'),
(34, 'ORD0034', 13, 3, 'C', 'sri.n@test.in'),
(35, 'ORD0035', 13, 3, 'C', 'sri.n@test.in'),
(36, 'ORD0036', 13, 3, 'C', 'sri.n@test.in'),
(37, 'ORD0037', 13, 3, 'C', 'sri.n@test.in'),
(38, 'ORD0038', 13, 3, 'C', 'sri.n@test.in'),
(39, 'ORD0039', 19, 3, 'C', 'sri.n@test.in'),
(40, 'ORD0040', 13, 3, 'C', 'sri.n@test.in'),
(41, 'ORD0041', 13, 3, 'C', 'sri.n@test.in'),
(42, 'ORD0042', 13, 3, 'C', 'sri.n@test.in'),
(43, 'ORD0043', 13, 3, 'C', 'sri.n@test.in'),
(44, 'ORD0044', 13, 3, 'C', 'sri.n@test.in'),
(45, 'ORD0045', 13, 3, 'C', 'sri.n@test.in'),
(46, 'ORD0046', 13, 3, 'C', 'sri.n@test.in'),
(47, 'ORD0047', 13, 3, 'C', 'sri.n@test.in'),
(48, 'ORD0048', 13, 3, 'C', 'sri.n@test.in'),
(49, 'ORD0049', 19, 3, 'C', 'sri.n@test.in'),
(51, 'ORD0051', 13, 3, 'C', 'sri.n@test.in'),
(52, 'ORD0052', 13, 3, 'C', 'sri.n@test.in'),
(53, 'ORD0053', 13, 3, 'C', 'sri.n@test.in'),
(54, 'ORD0054', 13, 10, 'G', 'sri.nas@test.in'),
(55, 'ORD0055', 13, 11, 'G', 'sri.nasqw@test.in'),
(56, 'ORD0056', 13, 12, 'G', 'sri.nqw@test.in'),
(57, 'ORD0057', 13, 13, 'G', 'sri.nas1123@test.in'),
(58, 'ORD0058', 13, 14, 'G', 'sri.nqw13@test.in'),
(59, 'ORD0059', 13, 15, 'G', 'sri.nas123@test.in'),
(60, 'ORD0060', 13, 16, 'G', 'sri.nas12345@test.in'),
(61, 'ORD0061', 13, 17, 'G', 'sri.nqw123@test.in'),
(62, 'ORD0062', 13, 18, 'G', 'sri.nas123111@test.in'),
(63, 'ORD0063', 13, 19, 'G', 'sri@test.in'),
(64, 'ORD0064', 13, 20, 'G', 'sri.nas111@test.in'),
(65, 'ORD0065', 13, 21, 'G', 'sri.nas12354klk@test.in'),
(66, 'ORD0066', 13, 22, 'G', 'sri.nas123879@test.in'),
(67, 'ORD0067', 13, 23, 'G', 'sri.nasasd@test.in'),
(68, 'ORD0068', 13, 24, 'G', 'sri.nasqwe@test.in'),
(69, 'ORD0069', 13, 25, 'G', 'sri.nas121212@test.in'),
(70, 'ORD0070', 13, 26, 'G', 'sri.nasqwqw@test.in'),
(71, 'ORD0071', 13, 27, 'G', 'sri.nqw321@test.in'),
(72, 'ORD0072', 13, 28, 'G', 'sri.nas123123@test.in'),
(73, 'ORD0073', 13, 3, 'C', 'sri.n@test.in'),
(74, 'ORD0074', 13, 3, 'C', 'sri.n@test.in'),
(75, 'ORD0075', 13, 3, 'C', 'sri.n@test.in'),
(76, 'ORD0076', 13, 3, 'C', 'sri.n@test.in'),
(77, 'ORD0077', 13, 3, 'C', 'sri.n@test.in'),
(78, 'ORD0078', 13, 3, 'C', 'sri.n@test.in'),
(79, 'ORD0079', 13, 3, 'C', 'sri.n@test.in'),
(121, 'ORD0121', 13, 52, 'G', 'sssri.n123123@test.in'),
(122, 'ORD0122', 13, 3, 'C', 'sri.n@test.in'),
(123, 'ORD0123', 13, 3, 'C', 'sri.n@test.in'),
(84, 'ORD0084', 13, 3, 'C', 'sri.n@test.in'),
(86, 'ORD0086', 13, 3, 'C', 'sri.n@test.in'),
(87, 'ORD0087', 13, 3, 'C', 'sri.n@test.in'),
(89, 'ORD0089', 13, 31, 'G', 'royalrenga@test.in'),
(90, 'ORD0090', 13, 32, 'G', 'nsri.n@test.in'),
(91, 'ORD0091', 13, 33, 'G', 'nnsri.n@test.in'),
(92, 'ORD0092', 13, 3, 'C', 'sri.n@test.in'),
(93, 'ORD0093', 13, 34, 'G', 'sssri.n@test.in'),
(94, 'ORD0094', 13, 35, 'G', 'qwsri.n@test.in'),
(95, 'ORD0095', 13, 36, 'G', 'snsri.n@test.in'),
(96, 'ORD0096', 13, 37, 'G', 'ncnsri.n@test.in'),
(97, 'ORD0097', 13, 38, 'G', 'nwnsri.n@test.in'),
(98, 'ORD0098', 13, 39, 'G', 'ncnasri.n@test.in'),
(99, 'ORD0099', 13, 40, 'G', 'ncnsasri.n@test.in'),
(100, 'ORD0100', 13, 41, 'G', 'ncqqnasri.n@test.in'),
(101, 'ORD0101', 13, 42, 'G', 'asdqazsri.nas123@test.in'),
(102, 'ORD0102', 13, 43, 'G', 'nacqqnasri.n@test.in'),
(103, 'ORD0103', 13, 3, 'C', 'sri.n@test.in'),
(104, 'ORD0104', 13, 3, 'C', 'sri.n@test.in'),
(105, 'ORD0105', 13, 3, 'C', 'sri.n@test.in'),
(106, 'ORD0106', 13, 3, 'C', 'sri.n@test.in'),
(107, 'ORD0107', 13, 3, 'C', 'sri.n@test.in'),
(108, 'ORD0108', 13, 0, 'G', 'sri.n@test.in'),
(109, 'ORD0109', 13, 3, 'C', 'sri.n@test.in'),
(110, 'ORD0110', 13, 3, 'C', 'sri.n@test.in'),
(111, 'ORD0111', 13, 44, 'G', 'qsw@test.in'),
(112, 'ORD0112', 13, 45, 'G', 'asdasd@test.in'),
(113, 'ORD0113', 13, 46, 'G', 'qweee@test.in'),
(114, 'ORD0114', 13, 47, 'G', 'qweqwe@test.in'),
(115, 'ORD0115', 13, 48, 'G', 'nsv123sri.n@test.in'),
(116, 'ORD0116', 13, 49, 'G', 'asdasdasd@test.in'),
(117, 'ORD0117', 13, 50, 'G', 'asdasdasdasd@test.in'),
(118, 'ORD0118', 13, 51, 'G', 'qwerew@test.in'),
(119, 'ORD0119', 13, 7, 'C', 'kvinocse86@test.in'),
(120, 'ORD0120', 13, 3, 'C', 'sri.n@test.in'),
(124, 'ORD0124', 13, 53, 'C', 'sri.n@test.in'),
(125, 'ORD0125', 13, 0, 'G', 'sri.n@test.in'),
(126, 'ORD0126', 13, 53, 'C', 'sri.n@test.in'),
(127, 'ORD0127', 13, 53, 'C', 'sri.n@test.in'),
(128, 'ORD0128', 13, 53, 'C', 'sri.n@test.in'),
(129, 'ORD0129', 13, 53, 'C', 'sri.n@test.in'),
(130, 'ORD0130', 13, 53, 'C', 'sri.n@test.in'),
(131, 'ORD0131', 13, 53, 'C', 'sri.n@test.in'),
(132, 'ORD0132', 13, 53, 'C', 'sri.n@test.in'),
(133, 'ORD0133', 13, 53, 'C', 'sri.n@test.in'),
(134, 'ORD0134', 13, 53, 'C', 'sri.n@test.in'),
(135, 'ORD0135', 13, 53, 'C', 'sri.n@test.in'),
(136, 'ORD0136', 13, 53, 'C', 'sri.n@test.in'),
(137, 'ORD0137', 13, 53, 'C', 'sri.n@test.in'),
(138, 'ORD0138', 13, 53, 'C', 'sri.n@test.in'),
(139, 'ORD0139', 13, 0, 'G', 'sri.n321@test.in'),
(140, 'ORD0140', 13, 53, 'C', 'sri.n123@test.in'),
(141, 'ORD0141', 13, 53, 'C', 'sri.n@test.in'),
(142, 'ORD0142', 13, 53, 'C', 'sri.n@test.in'),
(143, 'ORD0143', 13, 55, 'G', 'vesri@test.in'),
(144, 'ORD0144', 13, 56, 'G', 'vesri.n@test.in'),
(145, 'ORD0145', 13, 57, 'G', 'veesri.n@test.in'),
(146, 'ORD0146', 13, 58, 'G', 'veeisri.n@test.in'),
(147, 'ORD0147', 13, 59, 'G', 'ren@test.in'),
(148, 'ORD0148', 13, 60, 'G', 'ren1@test.in'),
(149, 'ORD0149', 13, 53, 'C', 'sri.n@test.in'),
(150, 'ORD0150', 13, 53, 'C', 'sri.n@test.in'),
(151, 'ORD0151', 13, 53, 'C', 'sri.n@test.in');

CREATE TABLE IF NOT EXISTS `my_restaurant` (
  `restaurant_id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_name` varchar(100) NOT NULL,
  PRIMARY KEY (`restaurant_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

INSERT INTO `my_restaurant` (`restaurant_id`, `restaurant_name`) VALUES
(1, 'Fuji Sushi'),
(2, 'Big Pete''s Pizza'),
(3, 'Vinos Pizza'),
(4, 'Wafaa and Mikes Cafe'),
(5, 'Fuji Sushi (San Marco Blvd)'),
(6, 'Midtown Deli + Cafe'),
(7, 'De Real Ting Cafe'),
(8, 'Alex Delicatessen'),
(9, 'Two Doors Down Restaurant'),
(10, 'Ginas Deli'),
(11, 'The Mudville Grille (Beach Blvd)'),
(12, 'Casbah Cafe'),
(13, 'Alexander Grill'),
(14, 'The Southern Grill'),
(15, 'Cool Moose Cafe'),
(16, 'Basil: Thai and Sushi'),
(17, 'Hot Wok'),
(18, 'China Joy'),
(19, 'Blu Diner'),
(21, 'New Test restaurant'),
(22, 'testing res');

我为客户在餐厅完成的大多数订单编写了以下 SQL。

SELECT o.customeremail AS custemail, o.restaurant_id, rest.restaurant_name, COUNT( o.customeremail ) AS totalordercount
FROM my_order AS o
INNER JOIN my_customer AS cust ON cust.customer_email = o.customeremail
INNER JOIN my_restaurant AS rest ON rest.restaurant_id = o.restaurant_id
WHERE o.orderid IS NOT NULL 
GROUP BY o.restaurant_id
ORDER BY totalordercount DESC

我得到的结果是这样的。

custemail           restaurant_id   restaurant_name            totalordercount
sri.n@test.in       13              Alexander Grill            79
vinothini.k@test.in 3               Vinos Pizza                12
sri.n@test.in       2               Big Pete's Pizza            5
sri.n@test.in       19              Blu Diner                   2
vinothini.k@test.in 1               Fuji Sushi                  1
sri.n@test.in       8               Alex Delicatessen           1
sri.n@test.in       5               Fuji Sushi (San Marco Blvd) 1

SELECT * 
FROM  `my_order` 
WHERE  `restaurant_id` =13
AND  `customeremail` =  'sri.n@test.in'
LIMIT 0 , 30

我从上面的查询中得到的只有 71 行。但我的查询显示第 79 行

sri.n@test.in       13              Alexander Grill            79

但我需要这样的输出。

custemail           restaurant_id   restaurant_name            totalordercount
sri.n@test.in       13              Alexander Grill            71
vinothini.k@test.in 3               Vinos Pizza                12
sri.n@test.in       2               Big Pete's Pizza            5
sri.n@test.in       19              Blu Diner                   2
vinothini.k@test.in 1               Fuji Sushi                  1
sri.n@test.in       8               Alex Delicatessen           1
sri.n@test.in       5               Fuji Sushi (San Marco Blvd) 1

提前致谢

Answer 1

首先，您滥用了 pernicious nonstandard MySQL extension to GROUP BY尝试在按餐厅聚合的结果集中显示客户电子邮件。您对此的滥用会导致您的结果集包含 custemail 的任意值。

其次，这个简单的查询显示 Alexander Grill 有 121 个订单。你所说的只有 71 个似乎是不正确的。

SELECT COUNT(*), o.restaurant_id, r.restaurant_name
  FROM my_order AS o
  LEFT JOIN my_restaurant AS r ON o.restaurant_id = r.restaurant_id
 GROUP BY o.restaurant_id, r.restaurant_name

第三，有相当多的订单的 customer_id 在您的客户表中找不到。您正在使用INNER JOIN，因此您的查询将被删除并且不会计算这些订单。 LEFT JOIN 将恢复那些删除的记录。但是，您仍然没有使用该 JOIN 的结果。

第四，您的 WHERE o.orderid IS NOT NULL 子句毫无意义:该列是主键。

尝试此查询，省略 my_customer 表 JOIN:

SELECT o.restaurant_id, 
       rest.restaurant_name, 
       COUNT( DISTINCT o.customer_id ) AS distinctcustomercount,
       COUNT( * ) AS totalordercount
  FROM my_order AS o
  LEFT JOIN my_restaurant AS rest ON rest.restaurant_id = o.restaurant_id
 GROUP BY o.restaurant_id, rest.restaurant_name  /* fix GROUP BY misuse */
 ORDER BY COUNT( * ) DESC, rest.restaurant_name 

我认为这对你有用。它完全指定了GROUP BY，并在只有一个订单的餐厅行之间进行某种排序。