mysql连续天数返回错误值

z4iuyo4d  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(326)

我试图在mysql中从timestamp列连续计算天数。我以为这个问题很久以前就解决过一次——但由于某种奇怪的原因,它返回了错误的值。
它应该返回一列,其中包含用户名、连续日期列表中时间值最高的最低日期和最大连续天数。
除了连续的最大天数不一定总是正确的以外,它都能工作。

SELECT username, day1, count(distinct date) days from (
    SELECT t1.username, t1.date, max(tmp.date) day1 from sites_surfed t1
        LEFT JOIN (
            SELECT tmp1.date from sites_surfed tmp1
            LEFT JOIN sites_surfed tmp2
                ON DATE_FORMAT(tmp1.date,'%d/%m/%Y') = DATE_FORMAT(tmp2.date,'%d/%m/%Y')+1
                AND tmp1.username = tmp2.username where (tmp1.username = 'viraladmin') 
                AND (tmp2.date is null))tmp on (t1.date >= tmp.date) where t1.username = 'viraladmin'
                GROUP BY t1.date
   ) fin  
   WEHERE username = 'viraladmin'
       GROUP BY day1 having count(distinct date) > 1 order by days

下面的jsfiddle显示了一个示例,它返回3表示连续的总天数,而它只应返回2,因为表列表中只有两个唯一的日期
http://sqlfiddle.com/#!9/85f6d6/4号
这是怎么回事,我该怎么解决?

eufgjt7s

eufgjt7s1#

我变了

GROUP BY t1.date

GROUP BY DATE_FORMAT(t1.date,'%d/%m/%Y')

这似乎奏效了。

SELECT username, day1, count(distinct date) days from (
    SELECT t1.username, t1.date, max(tmp.date) day1 from sites_surfed t1
        LEFT JOIN (
            SELECT tmp1.date from sites_surfed tmp1
            LEFT JOIN sites_surfed tmp2
                ON DATE_FORMAT(tmp1.date,'%d/%m/%Y') = DATE_FORMAT(tmp2.date,'%d/%m/%Y')+1
                AND tmp1.username = tmp2.username where (tmp1.username = 'viraladmin') 
                AND (tmp2.date is null))tmp on (t1.date >= tmp.date) where t1.username = 'viraladmin'
                GROUP BY DATE_FORMAT(t1.date,'%d/%m/%Y')
   ) fin  
   WHERE username = 'viraladmin'
       GROUP BY day1 having count(distinct date) > 1 order by days

相关问题