使用Count的SQL左连接
使用Count的SQL左连接
提问于 2013-04-16 13:32:13
我在做一个电子游戏库存网站。以下是我的数据库表的简化版本以及一些示例数据：
CREATE TABLE `platforms` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `platforms` VALUES(1, 'Nintendo');
INSERT INTO `platforms` VALUES(2, 'Super Nintendo');
INSERT INTO `platforms` VALUES(3, 'Nintendo 64');
--
CREATE TABLE `games` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`platform_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
INSERT INTO `games` VALUES(1, 1, 'Super Mario Bros.');
INSERT INTO `games` VALUES(2, 1, 'Super Mario Bros. 2');
INSERT INTO `games` VALUES(3, 2, 'Super Mario World');
INSERT INTO `games` VALUES(4, 2, 'Super Mario Kart');
INSERT INTO `games` VALUES(5, 3, 'Super Mario 64');
INSERT INTO `games` VALUES(6, 3, 'Mario Kart 64');
--
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL,
`password` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `users` VALUES(1, 'john_doe', '$2a$10$cQhc4VAXVMEyC1tA.VRoWunpNVi7392adacT/weVBzu6XGI6.Jx/K');
INSERT INTO `users` VALUES(2, 'jane_doe', '$2a$10$Ot2BmlT14hKDxHGIV8jBx.lW76HCWdwuOhNGIYrJO5O7BEtDUWLWu');
--
CREATE TABLE `games_users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`game_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `games_users` VALUES(1, 1, 1, '2013-04-12 12:18:09');
INSERT INTO `games_users` VALUES(2, 3, 1, '2013-04-12 12:18:42');
INSERT INTO `games_users` VALUES(3, 4, 1, '2013-04-12 12:19:13');
INSERT INTO `games_users` VALUES(4, 2, 2, '2013-04-12 12:19:32');如你所见，john_doe有1款任天堂游戏(超级马里奥兄弟)，2款超级任天堂游戏(超级马里奥世界和超级马里奥赛车)，0款任天堂64款游戏。jane_doe有1款任天堂游戏(超级马里奥兄弟2款)，0款超级任天堂游戏，0款任天堂64款游戏。
我想写一个特定于用户的查询，它将列出所有的控制台，还列出了用户为每个控制台拥有的游戏数量。
这是john_doe的结果：
platform.id: 1
platform.name: Nintendo
game_count: 1
platform.id: 2
platform.name: Super Nintendo
game_count: 2
platform.id: 3
platform.name: Nintendo 64
game_count: 0这是jane_doe的结果：
platform.id: 1
platform.name: Nintendo
game_count: 1
platform.id: 2
platform.name: Super Nintendo
game_count: 0
platform.id: 3
platform.name: Nintendo 64
game_count: 0我该怎么做呢？
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-04-16 13:48:04
SELECT p.id, p.name, IFNULL(t.cnt, 0)
FROM platforms p
LEFT JOIN (
SELECT g.platform_id as 'id', COUNT(g.id) as cnt
FROM users u
JOIN games_users gu ON gu.user_id = u.id
JOIN games g ON g.id = gu.game_id
WHERE u.username = "jane_doe"
GROUP BY g.platform_id
) t ON t.id = p.idStack Overflow用户
发布于 2013-04-16 13:40:32
SELECT p.id, p.name, COUNT(g.id) FROM users u
LEFT JOIN games_users gu ON u.id = gu.user_id
LEFT JOIN games g ON g.id = gu.game_id
LEFT JOIN platforms p ON g.platform_id = p.id
WHERE u.id = 1
GROUP BY p.id http://sqlfiddle.com/#!2/6104d/6
如果你想看到0，我想你必须做一些作弊，这就是我是如何做到的。我得到了user_id的和(因为它们都是相同的)，然后除以user_id。
SELECT p.id, p.name, IFNULL(SUM(user_id),0)/1 FROM platforms p
LEFT JOIN games g ON g.platform_id = p.id
LEFT JOIN games_users gu ON g.id = gu.game_id AND gu.user_id = 1
GROUP BY p.id, p.name
SELECT p.id, p.name, IFNULL(SUM(user_id),0)/2 FROM platforms p
LEFT JOIN games g ON g.platform_id = p.id
LEFT JOIN games_users gu ON g.id = gu.game_id AND gu.user_id = 2
GROUP BY p.id, p.name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16029456
复制相关文章
相似问题
腾讯云开发者
