加入时MySQL计数查询排除记录

Question

我有两个 MySQL 表:一个用于元素，另一个用于记录购买。我试图简单地列出项目表中的每个产品以及特定用户购买该项目的次数。如果他们购买了 0 次，我希望它仍然列出该项目，但说 0。我当前的查询似乎排除了 0 次购买的记录。

这是我当前的查询。我也尝试过 GROUP BY i.item_id:

SELECT i.item_id, i.item_name, i.item_sku, i.item_price, COUNT(p.item_id) as purchase_count 
FROM items i 
LEFT OUTER JOIN purchases p 
ON p.item_id = i.item_id 
WHERE p.user_id = '1' 
GROUP BY p.item_id
ORDER BY i.item_name ASC

下面是建表SQL:

CREATE TABLE `items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(100) NOT NULL,
  `item_sku` varchar(100) NOT NULL,
  `item_price` decimal(19,4) NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `items` VALUES(1, 'Item One', 'ITEM1', 99.9900);
INSERT INTO `items` VALUES(2, 'Item Two', 'ITEM2', 19.9900);
INSERT INTO `items` VALUES(3, 'Item Three', 'ITEM3', 10.9900);
INSERT INTO `items` VALUES(4, 'Item Four', 'ITEM4', 4.9900);

CREATE TABLE `purchases` (
  `purchase_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `purchase_date` datetime NOT NULL,
  PRIMARY KEY (`purchase_id`),
  KEY `user_id` (`user_id`,`item_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `purchases` VALUES(1, 1, 1, '2011-02-01 12:01:38');
INSERT INTO `purchases` VALUES(2, 1, 2, '2011-01-03 12:01:45');
INSERT INTO `purchases` VALUES(3, 1, 1, '2011-02-08 12:02:17');

感谢您的帮助!我认为作为左外连接，它会显示所有项目。

Answer 1

为此您可能需要一个子查询，因为对于您当前的查询，WHERE 子句会将您的结果限制为 purchase 表中 user_id = 1 的行。如果您从查询中删除 WHERE 子句，您将看到什么我是说。

我将立即使用代码更新此内容以支持使用子查询的解决方案。

更新以下是查询所需的 SQL 代码:

SELECT i.item_id, i.item_name, i.item_sku, i.item_price, 
(SELECT COUNT(*) FROM purchases where item_id=i.item_id and user_id='1') as purchase_count 
FROM items i
ORDER BY i.item_name ASC