在多个时间范围内从mysql取回数据 - fetching from mysql between multiple time range

fetching from mysql between multiple time range

我只能在mysql中选择两个日期

<?php
//db connection goes here
$query=mysql_query("select count(*)as total from table where DATE_FORMAT(column,'%H:%i:%s') between '06:00:00' and '07:00:00'")
$data=mysql_fetch_assoc($query);
if($data) {
echo ' '.$data['total'];
}
?>

我想在H:i:s中多次获取6:00:00-7:00:00 07:30:00-08:30:00 09:00:00-10:00:00，并获取像

这样的报告

               usercount    
06:00 - 07:00   count no of user
07:30 - 08:30   count no of user
09:00 - 10:00   count no of user

可以在单个查询中完成吗?

使用case语句，并使用HAVING来忽略其他时间范围(作弊-只是想避免在WHERE子句中重新检查时间范围):-

SELECT CASE
            WHEN DATE_FORMAT(column,'%H:%i:%s') BETWEEN '06:00:00' and '07:00:00'
                THEN '6:00:00-7:00:00'
            WHEN DATE_FORMAT(column,'%H:%i:%s') BETWEEN '07:30:00' and '08:30:00'
                THEN '07:30:00-08:30:00'
            WHEN DATE_FORMAT(column,'%H:%i:%s') BETWEEN '09:00:00' and '10:00:00'
                THEN '09:00:00-10:00:00'
            ELSE NULL
        END AS TimeRange,
        COUNT(*)
FROM table
GROUP BY TimeRange
HAVING TimeRange IS NOT NULL

EDIT -按要求从PHP中删除:-

<?php
$sql = "SELECT CASE
                    WHEN DATE_FORMAT(column,'%H:%i:%s') BETWEEN '06:00:00' and '07:00:00'
                        THEN '6:00:00-7:00:00'
                    WHEN DATE_FORMAT(column,'%H:%i:%s') BETWEEN '07:30:00' and '08:30:00'
                        THEN '07:30:00-08:30:00'
                    WHEN DATE_FORMAT(column,'%H:%i:%s') BETWEEN '09:00:00' and '10:00:00'
                        THEN '09:00:00-10:00:00'
                    ELSE NULL
                END AS TimeRange,
                COUNT(*) AS total
        FROM table
        GROUP BY TimeRange
        HAVING TimeRange IS NOT NULL";
$query = mysql_query($sql);
while($data = mysql_fetch_assoc($query))
{
    echo $data['TimeRange']."'t".$data['total']."'r'n";
}
?>

您可以使用group by和if()来使用它们，这将沿着

假设你的between(因为我不经常使用)语法是正确的，试试这个:

select count(*) as `total`,
IF(
   DATE_FORMAT(column,'%H:%i:%s') between '06:00:00' and '07:00:00',
   '06:00 - 07:00',
   IF(
      DATE_FORMAT(column,'%H:%i:%s') between '07:30:00' and '08:30:00',
      '07:30 - 08:30',
      IF(
         DATE_FORMAT(column,'%H:%i:%s') between '09:00:00' and '10:00:00',
         '09:00 - 10:00',
         'Other'
      )
   )
) AS `interval`
from `table`
group by `interval`

如果不行，试试:

select count(*) as `total`,
IF(
   DATE_FORMAT(column,'%H:%i:%s') between '06:00:00' and '07:00:00',
   '06:00 - 07:00',
   IF(
      DATE_FORMAT(column,'%H:%i:%s') between '07:30:00' and '08:30:00',
      '07:30 - 08:30',
      IF(
         DATE_FORMAT(column,'%H:%i:%s') between '09:00:00' and '10:00:00',
         '09:00 - 10:00',
         'Other'
      )
   )
) AS `interval`
from `table`
group by IF(
   DATE_FORMAT(column,'%H:%i:%s') between '06:00:00' and '07:00:00',
   '06:00 - 07:00',
   IF(
      DATE_FORMAT(column,'%H:%i:%s') between '07:30:00' and '08:30:00',
      '07:30 - 08:30',
      IF(
         DATE_FORMAT(column,'%H:%i:%s') between '09:00:00' and '10:00:00',
         '09:00 - 10:00',
         'Other'
      )
   )
)

http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html