如何使用BETWEEN在MySQL查询中相反？ [英] How to use opposite of BETWEEN in a MySQL query?

问题描述

我有一个工作查询，使用BETWEEN在日期范围内查找结果，我需要的只是相反的结果，而不是在给定的日期范围之间。以下是下面的查询。

  SELECT loanac.id，loanac.name，loanac.lacc，loanac.phone，
 SUM（loantrans.in）as totalin，SUM（loantrans.out）as totalout 
 
 FROM loanac，loantrans 
 WHERE（loanac.lacc = loantrans.account）
 AND（ 'loantrans`.`date` BETWEEN CAST（'$ range'as DATE）和CAST（'$ date'as DATE））
 
 GROUP BY loanac.lacc 
 HAVING SUM（IFNULL `loantrans`.`out`，0））> SUM（IFNULL（`loantrans`.`in，0））
  

任何人都可以帮助我找到相反的结果或结果不在相同条件的日期范围之间？

详细信息屏幕截图： https://www.dropbox.com/sh/lgmop3zxqaf9mjy/AAAWVWjZXd8da9KDDOqz2ANla?dl=0

解决方案

我唯一可以想到的是，不知何故，定义日期间隔的表达式返回一个时间段（那些 strtotime（）看起来像歹徒）。 这个答案处理两个选项（选项2是好的）当日期包括时间部分。

在具体情况下，我认为这将是解决问题的最佳方法：

  SELECT loanac.id，loanac.name，loanac.lacc，loanac.phone 
，SUM（loantrans.in）as totalin，SUM（loantrans.out）as totalout 
 FROM loanac 
 INNER JOIN loantrans on loanac.lacc = loantrans.account 
  - 代替WHERE子句中的隐式加入，使用显式INNER JOIN 
 WHERE date（ loantrans.date） date（'$ range'）
 AND date（loantrans.date）> date（'$ date'）
 GROUP BY loanac.lacc 
 HAVING SUM（IFNULL（`loantrans`.`out`，0））> SUM（IFNULL（`loantrans`.`in`，0））
  

请注意， date（）函数删除值的时间段。

---

还有一件事：您的代码可能会受到 SQL注入攻击的攻击。请看看这里（幽默）的例子，以及有关如何处理的提示。

I have a working query which used "BETWEEN" to find results within a date range, what i need is just opposite result or not between the given date range. Here is the query below.

SELECT loanac.id, loanac.name, loanac.lacc, loanac.phone, 
SUM(loantrans.in) as totalin, SUM(loantrans.out) as totalout 

FROM loanac, loantrans 
WHERE (loanac.lacc=loantrans.account) 
AND (`loantrans`.`date` BETWEEN CAST('$range' AS DATE) AND CAST('$date' AS DATE))

GROUP BY loanac.lacc
HAVING SUM(IFNULL(`loantrans`.`out`,0)) > SUM(IFNULL(`loantrans`.`in`,0))

Can anyone help me finding opposite result or result of not between the date range having the same conditions?

Details Screenshots here: https://www.dropbox.com/sh/lgmop3zxqaf9mjy/AAAWVWjZXd8da9KDDOqz2ANla?dl=0

解决方案

The only thing I can think of is that, somehow, the expressions that define the date interval are returning a time section (those strtotime() look like the culprits). This answer deals with two options (option 2 is the good one) when the date includes a time part.

In your specific case, I think this would be the best approach to solve the issue:

SELECT loanac.id, loanac.name, loanac.lacc, loanac.phone
     , SUM(loantrans.in) as totalin, SUM(loantrans.out) as totalout 
FROM loanac
     INNER JOIN loantrans on loanac.lacc = loantrans.account
-- Instead of an implicit join in the WHERE clause, use an explicit INNER JOIN
WHERE date(loantrans.date) < date('$range')  
  AND date(loantrans.date) > date('$date')
GROUP BY loanac.lacc
HAVING SUM(IFNULL(`loantrans`.`out`,0)) > SUM(IFNULL(`loantrans`.`in`,0))

Notice that the date() function "removes" the time section of the value.

---

One more thing: Your code may be vulnerable to SQL Injection attacks. Please take a look here for a (humorous) example on what it is, and tips on how to deal with it.

这篇关于如何使用BETWEEN在MySQL查询中相反？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！