需求: 查询符合F3001_SECOND为一小时以内的数量, 修改前代码 SELECT count(1) FROM iecp.tb3001_historyevent h, iecp.tb1001_container c WHERE ( h.F4201_TYPEID = 16 AND h.F4204_SORTID = 6 AND h.F3001_FLAG = 1 AND h.F3001_GROUPCODE = c.F1001_CONCODE AND c.F1001_SRVTYPE = 01 ) OR ( h.F4201_TYPEID = 17 AND h.F4204_SORTID = 6 AND h.F3001_FLAG = 1 AND h.F3001_GROUPCODE = c.F1001_CONCODE AND c.F1001_SRVTYPE = 01 ) AND h.F3001_SECOND >= 1514850247 AND h.F3001_SECOND <= 1514853847
查询结果1552,而正确的结果应该为0,
错误发生在了后面拼接的and语句,时间限制只对第二个or()的查询起作用,没有对第一个or查询起作用
修改如下:
SELECTcount(1)FROMiecp.tb3001_historyevent h,iecp.tb1001_container cWHERE((h.F4201_TYPEID = 16AND h.F4204_SORTID = 6AND h.F3001_FLAG = 1AND h.F3001_GROUPCODE = c.F1001_CONCODEAND c.F1001_SRVTYPE = 01)OR (h.F4201_TYPEID = 17AND h.F4204_SORTID = 6AND h.F3001_FLAG = 1AND h.F3001_GROUPCODE = c.F1001_CONCODEAND c.F1001_SRVTYPE = 01))AND h.F3001_SECOND >= 1514850247AND h.F3001_SECOND <= 1514853847
结果正确为0
另附上mySQL优先级顺序
优先级 运算符
(最高) !
-(负号),~(按位取反)
^(按位异或)
*,/(DIV),%(MOD)
+,-
>>,<<
&
|
=(比较运算),<=>,<,<=,>,>=,!=,<>,IN,IS NULL,LIKE,REGEXP
BETWEEN AND,CASE,WHEN,THEN,ELSE
NOT
&&,AND
XOR
||,OR
(最低) =(赋值运算),:=
记一次mysql语句因为字符优先级的问题引起的查询结果不一致问题
最新推荐文章于 2024-01-05 14:24:34 发布