Mysql 关于 Group by 的查询问题请帮帮我..？

Question

我的 table 是:

CREATE TABLE IF NOT EXISTS `maf_game` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `maf_game` (`id`, `name`) VALUES
(1, 'Finders Keepers'),
(2, 'My american farm');

CREATE TABLE IF NOT EXISTS `maf_game_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` varchar(100) NOT NULL,
  `sessionid` varchar(100) NOT NULL,
  `gid` int(11) NOT NULL,
  `qid` int(11) NOT NULL,
  `result` varchar(50) NOT NULL,
  `accesstime` datetime NOT NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO `maf_game_stats` (`id`, `userid`, `sessionid`, `gid`, `qid`, `result`, `accesstime`) VALUES
(1, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 1, '1', '2013-03-22 18:26:41'),
(2, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 2, '1', '2013-03-22 18:26:43'),
(3, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 1, '1', '2013-03-22 18:27:15'),
(4, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 2, '1', '2013-03-22 18:27:17'),
(5, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 3, '0', '2013-03-22 18:27:21'),
(6, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 4, '1', '2013-03-22 18:27:23'),
(7, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 5, '1', '2013-03-22 18:28:28'),
(8, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 1, '1', '2013-03-22 18:29:01'),
(9, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 2, '1', '2013-03-22 18:29:02'),
(10, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 3, '1', '2013-03-22 18:29:07'),
(11, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 4, '0', '2013-03-22 18:29:09'),
(12, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 5, '1', '2013-03-22 18:29:10'),
(13, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 1, '0', '2013-03-22 18:42:22'),
(14, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 2, '0', '2013-03-22 18:42:24'),
(15, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 3, '0', '2013-03-22 18:42:26'),
(16, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 4, '0', '2013-03-22 18:42:27'),
(17, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 5, '0', '2013-03-22 18:42:29'),
(18, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 1, '1', '2013-03-22 18:55:07'),
(19, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 2, '1', '2013-03-22 18:55:08'),
(20, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 3, '1', '2013-03-22 18:55:14'),
(21, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 4, '1', '2013-03-22 18:55:15'),
(22, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 1, '1', '2013-03-22 18:58:50'),
(23, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 2, '1', '2013-03-22 18:58:52');

我的书面查询是:

SELECT D.gid, D.qid, D.result, COUNT(D.id) AS staus FROM 
(SELECT MIN(id) AS id, userid, gid, qid, result, accesstime FROM maf_game_stats WHERE gid=2 GROUP BY userid, qid) AS D 
GROUP BY D.qid, D.result

以上查询结果:

gid qid result  staus
2   1   0       1
2   1   1       2
2   2   0       1
2   2   1       2
2   3   0       1
2   3   1       2
2   4   0       2
2   4   1       1
2   5   0       1
2   5   1       1

它工作正常，但我想要的结果就像Ex:

1. 有多少学生在第一次尝试时就正确/错误地回答了每个问题？

结果

--------------------------------------------
gid qid Correct Incorrect
-------------------------------------------
1   1   3      10       
1   2   8      1        
1   3   2      5
1   4   1      7    
1   5   4      3    

请问有什么建议吗......？

谢谢!瓦桑斯
22MR2013

对不起，我忘了......谁做了第一次尝试

1. 有多少学生在第一次尝试时就正确/错误地回答了每个问题？

CREATE TABLE IF NOT EXISTS `maf_game` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(250) NOT NULL,  PRIMARY KEY (`id`)) ;INSERT INTO `maf_game` (`id`, `name`) VALUES(1, 'Finders Keepers'),(2, 'My american farm'),(3, 'Stewardship'),(4, 'Harvet This');CREATE TABLE IF NOT EXISTS `maf_game_stats` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `userid` varchar(100) NOT NULL,  `sessionid` varchar(100) NOT NULL,  `gid` int(11) NOT NULL,  `qid` int(11) NOT NULL,  `result` varchar(50) NOT NULL,  `accesstime` datetime NOT NULL,  PRIMARY KEY (`id`))  ;INSERT INTO `maf_game_stats` (`id`, `userid`, `sessionid`, `gid`, `qid`, `result`, `accesstime`) VALUES(1, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 1, '1', '2013-03-22 18:26:41'),(2, '514c550391709', 'dicuu6e5bhv9j8com9fq38ipe2', 1, 2, '1', '2013-03-22 18:26:43'),(3, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 1, '1', '2013-03-22 18:27:15'),(4, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 2, '1', '2013-03-22 18:27:17'),(5, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 3, '0', '2013-03-22 18:27:21'),(6, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 4, '1', '2013-03-22 18:27:23'),(7, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 1, 5, '1', '2013-03-22 18:28:28'),(8, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 1, '1', '2013-03-22 18:29:01'),(9, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 2, '1', '2013-03-22 18:29:02'),(10, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 3, '1', '2013-03-22 18:29:07'),(11, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 4, '0', '2013-03-22 18:29:09'),(12, '514c5521c63e1', 'f3ffkgsdejo9luulgfn2i5lv60', 2, 5, '1', '2013-03-22 18:29:10'),(13, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 1, '0', '2013-03-22 18:42:22'),(14, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 2, '0', '2013-03-22 18:42:24'),(15, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 3, '0', '2013-03-22 18:42:26'),(16, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 4, '0', '2013-03-22 18:42:27'),(17, '514c58b1372e7', 'e70qhplor1v1ht2o3f17e2l3j3', 2, 5, '0', '2013-03-22 18:42:29'),(18, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 1, '1', '2013-03-22 18:55:07'),(19, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 2, '1', '2013-03-22 18:55:08'),(20, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 3, '1', '2013-03-22 18:55:14'),(21, '514c5baf5ef35', 'hn7q2cd4575864pm6etkmkdfh2', 2, 4, '1', '2013-03-22 18:55:15'),(22, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 1, '1', '2013-03-22 18:58:50'),(23, '514c5c8f5b55e', 'a36enh6d78dm8cao4a3ibqj4s0', 1, 2, '1', '2013-03-22 18:58:52'),(24, '514c5c968de28', '5nfatkpl301k9povksp8seteu3', 3, 1, '1', '2013-03-22 20:06:29'),(25, '514c5c968de28', '5nfatkpl301k9povksp8seteu3', 3, 2, '1', '2013-03-22 20:06:31'),(26, '514c5c968de28', '5nfatkpl301k9povksp8seteu3', 3, 3, '1', '2013-03-22 20:06:34'),(27, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 1, '1', '2013-03-22 20:06:41'),(28, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 2, '1', '2013-03-22 20:06:43'),(29, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 3, '1', '2013-03-22 20:06:45'),(30, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 4, '1', '2013-03-22 20:06:47'),(31, '514c6c75405dc', 'jhpoub8ueva48ja5glvt9rhsl6', 3, 5, '1', '2013-03-22 20:06:49'),(32, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 1, '0', '2013-03-22 20:06:55'),(33, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 2, '1', '2013-03-22 20:06:56'),(34, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 3, '0', '2013-03-22 20:06:58'),(35, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 4, '0', '2013-03-22 20:06:59'),(36, '514c6c8351cf3', 'rfa7agnrt63m2rk58legl7fr95', 3, 5, '1', '2013-03-22 20:07:00'),(37, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 1, '0', '2013-03-23 10:26:56'),(38, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 2, '1', '2013-03-23 10:26:58'),(39, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 3, '0', '2013-03-23 10:26:59'),(40, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 4, '0', '2013-03-23 10:27:00'),(41, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 2, 5, '1', '2013-03-23 10:27:01'),(42, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 1, '1', '2013-03-23 10:27:04'),(43, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 2, '1', '2013-03-23 10:27:06'),(44, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 3, '1', '2013-03-23 10:27:09'),(45, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 4, '1', '2013-03-23 10:27:10'),(46, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 1, 5, '1', '2013-03-23 10:27:11'),(47, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 1, '1', '2013-03-23 10:27:14'),(48, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 2, '1', '2013-03-23 10:27:15'),(49, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 3, '1', '2013-03-23 10:27:18'),(50, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 4, '1', '2013-03-23 10:27:19'),(51, '514d3307066c4', 'kdmcg2lc3a0c1ts82860lt8fg1', 4, 5, '1', '2013-03-23 10:27:20'),(52, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 1, '1', '2013-03-23 10:27:40'),(53, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 2, '1', '2013-03-23 10:27:41'),(54, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 3, '1', '2013-03-23 10:27:43'),(55, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 4, '1', '2013-03-23 10:27:44'),(56, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 2, 5, '1', '2013-03-23 10:27:45'),(57, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 1, '1', '2013-03-23 11:04:50'),(58, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 2, '1', '2013-03-23 11:05:04'),(59, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 3, '1', '2013-03-23 11:05:06'),(60, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 4, '0', '2013-03-23 11:05:06'),(61, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 5, '0', '2013-03-23 11:05:07'),(62, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 1, '0', '2013-03-23 11:05:11'),(63, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 2, '1', '2013-03-23 11:05:12'),(64, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 3, '1', '2013-03-23 11:05:15'),(65, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 4, '1', '2013-03-23 11:05:19'),(66, '514d363fd70d1', 'h82qih1hb3spv0fk4k1h3cdg10', 1, 5, '1', '2013-03-23 11:05:20');

Answer 1

SELECT  GID, QID, 
        SUM(Result = 1) Correct,
        SUM(Result = 0) Incorrect
FROM    maf_game_stats
GROUP   BY GID, QID