我尝试将以下表合并到一个查询中(最终目标是从php输出csv文件):
用户
id, name, email
例如数据
1, John, email@email.com
2, Jane, email@email.com
问题-问题的静态列表,每个问题属于存储为“属性”的3种类型中的一种,即单个字母[a,b,c]
id, text, attribute
例如数据
1, How cool are dogs?, A
2, How cool are cats?, B
3, How cool are fish?, A
4, How cool are mice?, C
5, How cool are birds?, B
用户提问-答案为整数[1-5]
id, user_id, question_id, answer
例如数据
1, 1, 1, 2
2, 1, 2, 5
3, 1, 3, 1
4, 1, 4, 1
5, 1, 5, 4
6, 2, 1, 4
7, 2, 2, 1
8, 2, 3, 3
9, 2, 4, 2
10, 2, 5, 2
预期结果:
我尝试将所有这些数据组合为一个查询,每个用户的问题按属性分组,因此输出格式如下:
users.id, users.name, users.email , A_question_total, B_question_total, C_question_total
1, John , email@email.com, 3, 9, 1
2, Jane , email@email.com, 7, 3, 2
我目前拥有:
我尝试了以下问题,这些问题几乎都能满足我的需求:
我可以选择连接在一起的所有内容,但这会复制用户和问题,并且不会按用户/属性提供问题总数:
Select * FROM users
JOIN users_questions ON users.id = users_questions.user_id
JOIN questions ON questions.id = users_questions.question_id;
我也可以按user/attribute选择所有的问题总数,但是我必须分别获取用户,然后在php中将它们连接在一起。
SELECT questions.attribute, users_questions.user_id, SUM(users_questions.answer) AS `total` FROM `questions` LEFT OUTER JOIN `users_questions` ON questions.id = users_questions.`question_id` GROUP BY users_questions.user_id, questions.attribute;
我想知道是否有一种复杂的连接、分组、子查询等方法可以在一个查询中完成这一切。如何将上述两个查询组合起来,并将实质上独立的计算出的每一个用户的总“行”转换为列,这是我一直在努力的。
下面是示例数据的sql转储:
DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(30) DEFAULT NULL,
`attribute` enum('A','B','C') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `questions` (`id`, `text`, `attribute`)
VALUES
(1,'How cool are dogs?','A'),
(2,'How cool are cats?','B'),
(3,'How cool are fish?','A'),
(4,'How cool are mice?','C'),
(5,'How cool are birds?','B');
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users` (`id`, `name`, `email`)
VALUES
(1,'John','email@email.com'),
(2,'Jane','email@email.com');
DROP TABLE IF EXISTS `users_questions`;
CREATE TABLE `users_questions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned DEFAULT NULL,
`question_id` int(11) unsigned DEFAULT NULL,
`answer` tinyint(1) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users_questions` (`id`, `user_id`, `question_id`, `answer`)
VALUES
(1,1,1,2),
(2,1,2,5),
(3,1,3,1),
(4,1,4,1),
(5,1,5,4),
(6,2,1,4),
(7,2,2,1),
(8,2,3,3),
(9,2,4,2),
(10,2,5,2);
感谢您的帮助!
1条答案
按热度按时间rbl8hiat1#
我认为这个查询(sqlfiddle)可以解决您的问题:
输出: