我有这两张桌子:
CREATE TABLE `papers` (
`id` int(11) DEFAULT NULL,
`Paperti` varchar(255) DEFAULT NULL,
`Tow` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `papers` VALUES ('1', 'ti1', 'Article');
INSERT INTO `papers` VALUES ('2', 'ti2', 'Conference');
INSERT INTO `papers` VALUES ('3', 'ti3', 'Letter');
INSERT INTO `papers` VALUES ('4', 'ti4', 'Article');
INSERT INTO `papers` VALUES ('5', 'ti4', 'Letter');
CREATE TABLE `typeofwwork` (
`rec` int(11) DEFAULT NULL,
`tow` varchar(255) DEFAULT NULL,
`weight` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `typeofwwork` VALUES ('1', 'Letter', '5');
INSERT INTO `typeofwwork` VALUES ('2', 'Article', '1');
INSERT INTO `typeofwwork` VALUES ('3', 'Conference', '4');
现在你看,我有一个重量的 typewriter . 我想为每一张纸计算每一种类型的作品的数量,但当我计算了那张纸的较低权重的字体时,我不应该计算较高权重的字体.
我试过这个,但那个不对.
SELECT Paperti,a.tow,min(b.weight),count(*) from papers a JOIN typeofwork b on a.Tow=b.tow GROUP BY a.Paperti;
期望的结果应该是:
PaperTI Count ToW
ti1 1 Article
ti2 1 Conference
ti3 1 Letter
ti4 1 Article ... because Article-weight is lower than Letter-weight
这应该是第二个结果:
2 Article
1 Conference
1 Letter
我怎么能这么做呢?