如何在Sqlite中从表中获得大于0的值

Question

赫，

我有客人

  date   idClient   nameClient      OpenBal  open Payable  dr  cr  comPay  close

2016-10-5    CL-MK  Kashif  CL-MK   9000    33134    0    5000   0      0      0   
2016-10-5    CL-MK  Kashif  CL-MK    0      33134    0    6000   0      0      0
2016-10-5    CL-MA  Asim  CL-MA    -8000    33134    0      0    0      0      0
2016-10-5    CL-MA  Asim  CL-MA      0      33134    0    7000   0      0      0
2016-10-5    CL-MA  Asim  CL-MA      0      33134    0      0    0      0      0
2016-10-5    CL-MW  Waqar  CL-MW   4000     33134    0    5000   0      0      0
2016-10-5    CL-MW  Waqar  CL-MW     0      33134    0      0    0      0      0
2016-10-5    CL-MW  Waqar  CL-MW     0      33134    0    8000   0      0      0
2016-10-5    CL-MF  Fahad   CL-MF -7000     33134    0      0    0      0      0
2016-10-5    CL-MF  Fahad   CL-MF    0      33134    0      0    0      0      0
2016-10-5    CL-MF  Fahad   CL-MF    0      33134    0   10000 20000 150000    0
2016-10-5    CL-MF  Fahad   CL-MF    0      33134    0      0    0      0      0

我使用这个查询来和所有客户端。

SELECT idClient,nameMemb,min(OpenBal)as OpenBal,sum(open) as open,(min(openBal)+sum(open))as able,sum(re)as re ,sum(cr) as cr,sum(comPay)as comPay,(min(openBal)+sum(open)-sum(re)-sum(cr)+sum(comPay))as close from AddClient  WHERE strftime ('%m', date) = '10' group by nameMemb

在结果表中，关闭列有0和负值，我想得到大于零的值。

我试试这个

  SELECT idClient,nameMemb,min(OpenBal)as OpenBal,sum(open) as open,(min(openBal)+sum(open))as able,sum(re)as re ,sum(cr) as cr,sum(comPay)as comPay,(min(openBal)+sum(open)-sum(re)-sum(cr)+sum(comPay))as close from AddClient  WHERE strftime ('%m', date) = '10' and close > 0 group by nameMemb

但是这个查询没有给出结果。

Answer 1

我相信你需要在你的小组之后使用“拥有”条款。类似于：

SELECT 
  idClient,nameMemb,min(OpenBal)as OpenBal,sum(open) as open,(min(openBal)+sum(open))as able,sum(re)as re ,sum(cr) as cr,sum(comPay)as comPay,(min(openBal)+sum(open)-sum(re)-sum(cr)+sum(comPay))as close
from 
  AddClient  
WHERE 
  strftime ('%m', date) = '10' 
group by 
  nameMemb 
having 
  close > 0