mysql软件价格 搜索,MySQL帮助：如何查找直到价格< = 20和status =’unpaid’的所有客户订单…

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

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/p>

Please help.. Thanks!

解决方案

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

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树首页概览31438 人正在系统学习中 相关资源：ExWinner成套报价软件

来源：唐僧同志