カスタム列外接続クエリー文

1003 ワード

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