mysql 这样的语句怎样写成一条语句

Question

    SELECT count(1) as count0 FROM test WHERE time >= '2017-11-23 00:00:00' AND time <= '2017-11-23 17:50:07';
    SELECT count(1) as count1 FROM test WHERE time >= '2017-11-22 00:00:00' AND time <= '2017-11-22 23:59:59';
    SELECT count(1) as count2 FROM test WHERE time >= '2017-11-21 00:00:00' AND time <= '2017-11-21 23:59:59';
    SELECT count(1) as count3 FROM test WHERE time >= '2017-11-20 00:00:00' AND time <= '2017-11-20 23:59:59';
    SELECT count(1) as count4 FROM test WHERE time >= '2017-11-19 00:00:00' AND time <= '2017-11-19 23:59:59';
    SELECT count(1) as count5 FROM test WHERE time >= '2017-11-18 00:00:00' AND time <= '2017-11-18 23:59:59';
    SELECT count(1) as count6 FROM test WHERE time >= '2017-11-17 00:00:00' AND time <= '2017-11-17 23:59:59';
    
    
    现在写在一块是这样：
    
    
SELECT
          count(ars0.id) as count0,
          count(ars1.id) as count1,
          count(ars2.id) as count2,
          count(ars3.id) as count3,
          count(ars4.id) as count4,
          count(ars5.id) as count5,
          count(ars6.id) as count6
      FROM
          test ars
 LEFT JOIN
          test ars0 ON ars.id = ars0.id AND ars0.time >= '2017-11-23 00:00:00' AND ars0.time <= '2017-11-23 17:54:21'
 LEFT JOIN
          test ars1 ON ars.id = ars1.id AND ars1.time >= '2017-11-22 00:00:00' AND ars1.time <= '2017-11-22 23:59:59'
 LEFT JOIN
          test ars2 ON ars.id = ars2.id AND ars2.time >= '2017-11-21 00:00:00' AND ars2.time <= '2017-11-21 23:59:59'
 LEFT JOIN
          test ars3 ON ars.id = ars3.id AND ars3.time >= '2017-11-20 00:00:00' AND ars3.time <= '2017-11-20 23:59:59'
 LEFT JOIN
          test ars4 ON ars.id = ars4.id AND ars4.time >= '2017-11-19 00:00:00' AND ars4.time <= '2017-11-19 23:59:59'
 LEFT JOIN
          test ars5 ON ars.id = ars5.id AND ars5.time >= '2017-11-18 00:00:00' AND ars5.time <= '2017-11-18 23:59:59'
 LEFT JOIN
          test ars6 ON ars.id = ars6.id AND ars6.time >= '2017-11-17 00:00:00' AND ars6.time <= '2017-11-17 23:59:59'
  
  
  

总感觉这样写还没PHP的多条查询快呢？并且感觉这样写问题比较大，但是也不知道咋改~~~求大神

Answer 1

SELECT count(*) FROM test GROUP BY DATE_FORMAT(time ,'%Y-%m-%d');

如果只要某个范围内的天数可以在加一个
where
time>='startday' AND time<='endday'