MySQL BETWEEN和“> = AND< ="给出不同的结果 [英] MySQL BETWEEN and ">= AND <=" giving different results

问题描述

MySQL BETWEEN运算符和使用> =< ="之间有什么区别吗?

当我在此查询上尝试使用它们时，它们给出不同的结果.

SELECT 
      tblStaff.TitleGredCd,
      count(StudCourse1.StaffNoIC),     
      StudCourse1.StaffNoIC,
      StaffNm,
      BranchNm, 
      StDt, 
      EndDt, 
      SUM(datediff(EndDt,StDt)+1)  
      TotalDay, 
      (SELECT SUM( pembelajaranhour ) 
         FROM tblpembelajaran d 
         WHERE d.StaffNoIC = tblStaff.StaffNoIC
           AND YEAR( PembelajaranDate ) = 2011 )Totalhour,
      (SELECT SUM( datediff( kendiridate, kendiridate ) +1 ) 
         FROM tblkendiri d 
         WHERE d.StaffNoIC = tblStaff.StaffNoIC 
           AND YEAR( kendiridate ) = 2011 ) Totalkendiriday
   FROM 
      StudCourse1, 
      tblStaff 
         LEFT OUTER JOIN tblRefBranch 
            ON tblStaff.BranchCd = tblRefBranch.BranchCd, 
      tblRefTitleGred
   WHERE 
          StudCourse1.StaffNoIC = tblStaff.StaffNoIC 
      AND tblStaff.TitleGredCd = tblRefTitleGred.TitleGredCd 
     [Date Condition]
   GROUP BY 
      tblStaff.TitleGredCd, 
      StudCourse1.StaffNoIC

如果将AND StDt BETWEEN '2011-1-1' AND '2011-12-31'用作[Date Condition]，则会得到6个返回结果. 但是，如果将AND StDt >= '2011-1-1' AND EndDt '2011-12-31'用作[Date Condition]，则会得到7个返回结果. 任何建议将不胜感激.

解决方案

BETWEEN与DATE类型有点奇怪.您所说的日期类型是

// These 2 are the same
BETWEEN '2011-01-01' AND '2011-12-31'
BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 00:00:00'

，因此它只能匹配到'2011-12-30 23:59:59'，或者换句话说，它与DATE字段不匹配'2011-12-30'.

当您搜索'2011-01-01'< = ....< ='2011-12-31'时，它会正确匹配可能是=='2011-12-31'的日期

因此，您有4个选择.第一个是在上限上增加1天. 这是我通常使用的选项 BETWEEN在date列上的索引非常好玩，并且数据行上没有使用CAST或DATE()，因为ADDDATE('2011-12-31',1)只需要执行一次

WHERE `StDt` BETWEEN '2011-01-01' AND ADDDATE('2011-12-31',1)

第二个是明确指定时间戳记

WHERE `StDt`BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 23:59:59'

第三个，如您所见，是使用< =和> =

WHERE `StDt` >= '2011-01-01' AND `StDt` <= '2011-12-31'

以下似乎也可行的第四个选项，但是我仍然更喜欢第一种方法，因为它是我所知道的方法+不需要在每一行上使用CAST或DATE

WHERE DATE(`StDt`) BETWEEN DATE('2011-01-01') AND DATE('2011-12-31')

我希望这对您有帮助

Is there any difference between MySQL BETWEEN operator and using ">= <="?

When i tried them on this query, they give different results.

SELECT 
      tblStaff.TitleGredCd,
      count(StudCourse1.StaffNoIC),     
      StudCourse1.StaffNoIC,
      StaffNm,
      BranchNm, 
      StDt, 
      EndDt, 
      SUM(datediff(EndDt,StDt)+1)  
      TotalDay, 
      (SELECT SUM( pembelajaranhour ) 
         FROM tblpembelajaran d 
         WHERE d.StaffNoIC = tblStaff.StaffNoIC
           AND YEAR( PembelajaranDate ) = 2011 )Totalhour,
      (SELECT SUM( datediff( kendiridate, kendiridate ) +1 ) 
         FROM tblkendiri d 
         WHERE d.StaffNoIC = tblStaff.StaffNoIC 
           AND YEAR( kendiridate ) = 2011 ) Totalkendiriday
   FROM 
      StudCourse1, 
      tblStaff 
         LEFT OUTER JOIN tblRefBranch 
            ON tblStaff.BranchCd = tblRefBranch.BranchCd, 
      tblRefTitleGred
   WHERE 
          StudCourse1.StaffNoIC = tblStaff.StaffNoIC 
      AND tblStaff.TitleGredCd = tblRefTitleGred.TitleGredCd 
     [Date Condition]
   GROUP BY 
      tblStaff.TitleGredCd, 
      StudCourse1.StaffNoIC

If I use AND StDt BETWEEN '2011-1-1' AND '2011-12-31' for [Date Condition] I get 6 returned results. But if I use AND StDt >= '2011-1-1' AND EndDt '2011-12-31' for [Date Condition] I get 7 returned results. Any advice would be greatly appreciated.

解决方案

BETWEEN is a bit odd with DATE types. What you are saying with your date types is

// These 2 are the same
BETWEEN '2011-01-01' AND '2011-12-31'
BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 00:00:00'

and so it only match up to '2011-12-30 23:59:59', or to put it another way on a DATE field it won't match '2011-12-30'.

When you search for '2011-01-01' <= .... <= '2011-12-31' it then correctly matches the date being potentially == '2011-12-31'

So you have 4 options. The first one is to add 1 day onto the upper bound. This is the option I usually utilise The BETWEEN plays very nicely with an index on the date column, and there's no CAST or DATE() used on the data rows as the ADDDATE('2011-12-31',1) only needs executing once

WHERE `StDt` BETWEEN '2011-01-01' AND ADDDATE('2011-12-31',1)

The second is to explicitly specify the timestamp

WHERE `StDt`BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 23:59:59'

The third, as you've found, is to use <= and >=

WHERE `StDt` >= '2011-01-01' AND `StDt` <= '2011-12-31'

A fourth option that also seems to work is the following, but I still prefer the first method as it's the one I know works + doesn't require using CAST or DATE on every row

WHERE DATE(`StDt`) BETWEEN DATE('2011-01-01') AND DATE('2011-12-31')

I hope this is of help

这篇关于MySQL BETWEEN和“&gt; = AND&lt; ="给出不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！