select t.name, minNum as '800-2000', maxNum as '2001-5000' from
(select test.name,count(*) as minNum from test where category_id between 800 and 2000 group by test.name) t
join
(select test.name,count(*) as maxNum from test where category_id between 2001 and 5000 group by test.name) t1
on t.name = t1.name
显示成为
name 800-2000 2001-5000
------ ---------- ----------
aa 5 1
bb 2 3
cc 1 2
建表语句:
CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`category_id` int(10) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8