MySQL help: How to find all orders from a customer

问题:

I think my question will be understood better by providing the following example:

i have a table with following data:

orderid  Price username paymentstatus
1        10    john     unpaid
2        10    john     unpaid
4        10    john     unpaid
5        10    john     unpaid
6        10    sam      unpaid
7        10    john     unpaid
8        10    john     paid

I want to find all orders of (username) john where paymentstatus is "unpaid" and price total is <= 20

So the result should show following 2 lines (as order total is $20 and payment status is unpaid and username is john)

EXPECTED RESULT:
==
orderid  Price username paymentstatus
1        10    john     unpaid  
2        10    john     unpaid

==  

I hope you got my point/question?

Please help.. Thanks!

回答1:

Almost EXACT same question answered here. The premise is you would need another column to act as a running total for the customer in question...

I created table and simulated data exactly like your results and came up with YOUR exact results... The issue was somehow MySQL was applying the criteria TWICE per row and didn't understand how or why... I STRONGLY suspect its a bug, but can't describe it. Anyhow, I DO have a fix that forces an inner "PreQuery" as the basis, and return ALL records from that with the @SQLVars and then apply a WHERE clause from that...

select properSummed.*
   from 
      ( select
              o.orderid, 
              o.price, 
              @RunningTotal := @RunningTotal + o.price as UnpaidSoFar
           from
              orders o, 
              (select @RunningTotal := 0 ) sqlvars
           where o.ownerid = 1
             and o.paymentstatus = 'unpaid' ) properSummed
    where 
       properSummed.UnpaidSoFar <= 50

回答2:

Maybe this?

SELECT *
FROM order_table
WHERE username = 'john' 
AND paymentstatus = 'unpaid'
AND Price <= 20

But given your criteria, I would expect the results to also include orders 4, 5 and 7.

回答3:

what about this:

select *
from `orders`
where paymentstatus='unpaid' and username='john'
having sum(price) <= 20