CREATE TABLE IF NOT EXISTS `stats_download` (
  `record_id` int(11) NOT NULL auto_increment,
  `product_id` int(11) NOT NULL,
  `format_id` int(1) NOT NULL,
  `pnr_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL default '1',
  `download_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`record_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=79752 ;

SELECT `sd`.`product_id`, COUNT(*) AS `count_all`, `f1`.`count_1`, `f2`.`count_2`, `f3`.`count_3`, `f4`.`count_4`, `f5`.`count_5` FROM `stats_download` AS `sd`  
                INNER JOIN (  
                SELECT `product_id`, COUNT(*) AS `count_1`   
                FROM `stats_download`   
                WHERE `format_id` = 1 AND `pnr_id`='1' AND (`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-31 23:59:59' ) 
                GROUP BY `product_id`  
                ) AS `f1` ON `sd`.`product_id` = `f1`.`product_id`   
                INNER JOIN (  
                SELECT `product_id`, COUNT(*) AS `count_2`   
                FROM `stats_download`   
                WHERE `format_id` = 2 AND `pnr_id`='1' AND (`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-31 23:59:59' ) 
                GROUP BY `product_id`  
                ) AS `f2` ON `sd`.`product_id` = `f2`.`product_id`   
                INNER JOIN (  
                SELECT `product_id`, COUNT(*) AS `count_3`   
                FROM `stats_download`   
                WHERE `format_id` = 3 AND `pnr_id`='1' AND (`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-31 23:59:59' ) 
                GROUP BY `product_id`  
                ) AS `f3` ON `sd`.`product_id` = `f3`.`product_id`  
                INNER JOIN (  
                SELECT `product_id`, COUNT(*) AS `count_4`   
                FROM `stats_download`   
                WHERE `format_id` = 4 AND `pnr_id`='1' AND (`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-31 23:59:59' ) 
                GROUP BY `product_id`  
                ) AS `f4` ON `sd`.`product_id` = `f4`.`product_id`    
                INNER JOIN (  
                SELECT `product_id`, COUNT(*) AS `count_5`   
                FROM `stats_download`   
                WHERE `format_id` = 5 AND `pnr_id`='1' AND (`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-31 23:59:59' ) 
                GROUP BY `product_id`  
                ) AS `f5` ON `sd`.`product_id` = `f5`.`product_id`  
                WHERE `sd`.`pnr_id`='1' and (`sd`.`download_date` > '2010-05-01 00:00:01' AND `sd`.`download_date` < '2010-05-31 23:59:59' )  
                GROUP BY `product_id` ORDER BY `count_all` DESC LIMIT 0,10 

`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-01 23:59:59'

Спустя 41 минута, 40 секунд (10.08.2010 - 17:31) anonymouse написал(а):

SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2009-01-01 00:00:00'

Если брать этот пример с сайта _http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

То ДА - я считаю что время надо вводить что бы получался интервал от начала дня и до конца его.

Если не прав уж поправьте меня.

(`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-31 23:59:59' )

(`download_date` > '2010-05-01 00:00:01' AND `download_date` < '2010-05-31 23:59:59' )

Спустя 56 минут, 36 секунд (11.08.2010 - 11:36) linker написал(а):

гы, точно

Спустя 3 дня, 2 часа, 47 минут, 9 секунд (14.08.2010 - 14:23) uWeb написал(а):