用“x Days Old”列出结果(List results by “x Days Old”)

用“x Days Old”列出结果(List results by “x Days Old”)

我有一个包含应用程序的表，当他们联系时，“已联系”列标有“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

I have a table with applications, when they've been contacted the "contacted" column is marked with a "1". What I want to do if possible is count how many of the results are up to 3 days old, how many of them are 4-6 days old and how many of them are 7 or more days old. Is this possible in a single query?

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

EDIT:

Using the following:

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 ;

Then

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

I'm getting:

0 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 ;

You could use a select case and group by

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 ;

In the column my_result you should find the string you need

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 ;