按"x天“列出结果

Question

我有一个有应用程序的表格，当他们被联系时，“已联系”列被标记为"1“。如果可能的话，我想做的是数一数结果中有多少是3天前的，多少是4-6天的，还有多少是7天或更长的。这在一个查询中是可能的吗？

 __________________________________
| Name | Contacted |    Date       |
 ----------------------------------
| Bob  |      1    |  2016-09-16   |
| Ben  |      1    |  2016-10-03   |
| Sam  |      1    |  2016-10-03   |

编辑：

使用以下方法：

SELECT 
case  
    when datediff( CURDATE(), `submission_date`)  = 3 then '3 Days'
    when datediff( CURDATE(), `submission_date`)  between 4 and 6 then '4-6 Days'
    when datediff( CURDATE(), `submission_date`)  > 6 then  '7 or more days'
end as `days`,
sum( case  
    when datediff( CURDATE(), `submission_date`)  = 3 then 1 
    when datediff( CURDATE(), `submission_date`)  between 4 and 6 then 1
    when datediff( CURDATE(), `submission_date`)  > 6 then 1
else 0 
end  ) as tot 
FROM my_table 
GROUP BY
case  
    when datediff( CURDATE(), `submission_date`)  = 3 then '3 Days'
    when datediff( CURDATE(), `submission_date`)  between 4 and 6 then '4-6 Days'
    when datediff( CURDATE(),`submission_date`) > 6 then  '7 or more days'
end ;

然后

echo '<p>'.$row['tot'].'</p>';

我得到了：

0
1

Answer 1

您可以使用选择的大小写和组

 select 
     case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(), `date`)  > 6 then  '7 or more days'
      end  as `days`,
    sum( case  
        when datediff( CURDATE(), `date`)  <= 3 then 1 
        when datediff( CURDATE(), `date`)  between 4 and 6 then 1
        when datediff( CURDATE(), `date`)  > 6 then 1
        else 0 
    end  ) as tot 
 from my_table 
 where contacted = 1
 group by 
      case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(),`date`) > 6 then  '7 or more days'
      end ;

在列my_result中，您应该找到所需的字符串

   select  concat( 'You have *** ',  sum( case  
        when datediff( CURDATE(), `date`)  <= 3 then 1 
        when datediff( CURDATE(), `date`)  between 4 and 6 then 1
        when datediff( CURDATE(), `date`)  > 6 then 1
        else 0 
    end  ), ' *** New Items more than ' , 
     case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days old'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(), `date`)  > 6 then  '7 or more days'
      end ) as my_result 
 from my_table 
 where contacted = 1
 group by 
      case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(),`date`) > 6 then  '7 or more days'
      end ;