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和“> = AND< ="给出不同的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!