用“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 1I 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 ;更多推荐
发布评论