自定义列外连接查询语句

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