mysql获取相反结果,如何使用BETWEEN在MySQL查询中相反？

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?

解决方案

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.