使用Mysql时间数据类型的时间段

Question

i wish to query my sales table and get the count of distinct receipt numbers by hour for each day of the week

each record has a receipt number that does repeat, a datein in field that is just date data type, and a sepearte time field in the time datatype

here is the query i'm trying to use

select dayname(datein),dayofweek(datein), 
if(hour(timein) between 9 and 11, count(distinct salenbr),0) as `9-11`,
if(hour(timein) between 11 and 13, count(distinct salenbr),0) as `11-13`,
if(hour(timein) between 13 and 15, count(distinct salenbr),0) as `13-15`,
if(hour(timein) between 15 and 17, count(distinct salenbr),0) as `15-17`,
if(hour(timein) between 17 and 19, count(distinct salenbr),0) as `17-19`,
if(hour(timein) between 19 and 21, count(distinct salenbr),0) as `19-21`
from carmanslive.allsales 
where loccode = 'SF' and exitcode in ('64','65')  
and deptname = 'Photofinishing' 
and datein >=  DATE_SUB(NOW(), INTERVAL 3 MONTH) 
group by dayname(datein),dayofweek(datein) 
order by dayofweek(datein)

the output is wrong though

<table caption="UnknownTable (7 rows)">
  <thead>
    <tr>
      <th class="col0">dayname(datein)</th>
      <th class="col1">dayofweek(datein)</th>
      <th class="col2">9-11</th>
      <th class="col3">11-13</th>
      <th class="col4">13-15</th>
      <th class="col5">15-17</th>
      <th class="col6">17-19</th>
      <th class="col7">19-21</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td class="col0">Sunday</td>
      <td class="col1">1</td>
      <td class="col2">0</td>
      <td class="col3">118</td>
      <td class="col4">0</td>
      <td class="col5">0</td>
      <td class="col6">0</td>
      <td class="col7">0</td>
    </tr>
    <tr>
      <td class="col0">Monday</td>
      <td class="col1">2</td>
      <td class="col2">0</td>
      <td class="col3">0</td>
      <td class="col4">360</td>
      <td class="col5">360</td>
      <td class="col6">0</td>
      <td class="col7">0</td>
    </tr>
    <tr>
      <td class="col0">Tuesday</td>
      <td class="col1">3</td>
      <td class="col2">0</td>
      <td class="col3">390</td>
      <td class="col4">0</td>
      <td class="col5">0</td>
      <td class="col6">0</td>
      <td class="col7">0</td>
    </tr>
    <tr>
      <td class="col0">Wednesday</td>
      <td class="col1">4</td>
      <td class="col2">480</td>
      <td class="col3">0</td>
      <td class="col4">0</td>
      <td class="col5">0</td>
      <td class="col6">0</td>
      <td class="col7">0</td>
    </tr>
    <tr>
      <td class="col0">Thursday</td>
      <td class="col1">5</td>
      <td class="col2">0</td>
      <td class="col3">0</td>
      <td class="col4">0</td>
      <td class="col5">330</td>
      <td class="col6">0</td>
      <td class="col7">0</td>
    </tr>
    <tr>
      <td class="col0">Friday</td>
      <td class="col1">6</td>
      <td class="col2">0</td>
      <td class="col3">380</td>
      <td class="col4">380</td>
      <td class="col5">0</td>
      <td class="col6">0</td>
      <td class="col7">0</td>
    </tr>
    <tr>
      <td class="col0">Saturday</td>
      <td class="col1">7</td>
      <td class="col2">0</td>
      <td class="col3">0</td>
      <td class="col4">368</td>
      <td class="col5">0</td>
      <td class="col6">0</td>
      <td class="col7">0</td>
    </tr>
  </tbody>
</table>

there should be data in each column, i've confirmed the data is there.

Answer 1

I assume you have to put the aggregate around your if. Something like:

select dayname(datein),dayofweek(datein), 
count(distinct if(hour(timein) between 9 and 11, salenbr,0)) as `9-11`
[...]