如何获得大于零值的sqlserver [英] How to get greater than zero value sqlserver

问题描述

我在sqlserver中进行了查询。代码如下。

 选择 ReceiptNo，S_Adm_No，paid_date， S_Adm_No，Class，S_Name，S_F_Name，Mode_Payment，ChqNo，ChqDated，bank_Name，For_the_Month，
 FHeadId，FHead，Fine，TotalAmount，Concession，payableAmount，SUM（4月+ 5月+ 6月+ 7月+ 8月+ 9月+ 10月+ Nov + [Dec] + Jan + Feb + Mar） as  FEE 
 来自 View_Fee_Receipt_Students 其中 ReceiptNo = '  AIS / 16-17 / 00096'  group   by  ReceiptNo，
 S_Adm_No，paid_date，S_Adm_No，Class，S_Name，S_F_Name，Mode_Payment ，ChqNo，ChqDated，bank_Name，For_the_Month，
 Fine，TotalAmount，FHeadId，FHead，Concession，payableAmount 

我的问题是，如何在费用大于零的情况下获得价值...



我有什么尝试过：



............................ .............................

解决方案

你的意思是

 ...  WHERE  ReceiptNo = '  AIS / 16-17 / 00096'  GROUP   BY  ... 具有 SUM（Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + [Dec] + Jan + Feb + Mar）>  0  

但是......您确实意识到GROUP BY中的每个子句都会增加多个单独的组生成了，不是吗？当您使用多个分组字段时，SQL会为任何命名字段中具有不同值的每个行创建一个组。由于您的GROUP BY中的字段数量，我怀疑您将为输入表中的每一行获取一个单独的组...

您可以尝试使用以下查询





选择*从（

选择ReceiptNo，S_Adm_No，paid_date，S_Adm_No，Class，S_Name，S_F_Name，Mode_Payment，ChqNo ，ChqDated，bank_Name，For_the_Month，

FHeadId，FHead，Fine，TotalAmount，Concession，payableAmount，SUM（isnull（Apr，0）+ isnull（May，0）+ isnull（Jun，0）+ ISNULL（七月，0）+ ISNULL（八月，0）+ ISNULL（九月，0）+ ISNULL（十月，0）+ ISNULL（十一月，0）+ ISNULL（[DEC]，0）+ ISNULL（一月，0） + isnull（Feb，0）+ isnull（Mar，0））作为FEE

来自View_Fee_Receipt_Students，其中ReceiptNo ='AIS / 16-17 / 00096'分组由ReceiptNo，

S_Adm_No，paid_date，S_Adm_No，Class，S_Name，S_F_Name，Mode_Payment，ChqNo，ChqDated，bank_Name，For_the_Month，

罚款，TotalAmount，FHeadId，FHead，Concession，payableAmount）结果



WHERE FEE> 0

I have made a query in sqlserver. the code is below.

select ReceiptNo, S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month , 
FHeadId, FHead,Fine, TotalAmount, Concession, payableAmount, SUM (Apr + May+Jun+Jul+Aug+Sep+Oct+Nov+[Dec]+Jan+Feb+Mar) as FEE 
from View_Fee_Receipt_Students where ReceiptNo='AIS/16-17/00096' group by ReceiptNo, 
S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month ,
Fine, TotalAmount, FHeadId, FHead, Concession, payableAmount

My question is, how to get value where fee has greater than zero...

What I have tried:

.........................................................

解决方案

Do you mean

... WHERE ReceiptNo='AIS/16-17/00096' GROUP BY ... HAVING SUM (Apr + May+Jun+Jul+Aug+Sep+Oct+Nov+[Dec]+Jan+Feb+Mar) > 0

But...You do realise that each clause in a GROUP BY increases teh number of separate groups generated, don't you? When you use more than one grouping field, SQL creates a group for each of the rows with distinct values in any of the named fields. And with the number of fields in your GROUP BY, I suspect you will get a separate group for each row in the input table...

you can try with below query


Select * from (
select ReceiptNo, S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month ,
FHeadId, FHead,Fine, TotalAmount, Concession, payableAmount, SUM (isnull(Apr,0) + isnull(May,0) +isnull(Jun,0)+isnull(Jul,0)+isnull(Aug,0)+isnull(Sep,0)+isnull(Oct,0)+isnull(Nov,0)+isnull([Dec],0)+isnull(Jan,0)+isnull(Feb,0)+isnull(Mar,0)) as FEE
from View_Fee_Receipt_Students where ReceiptNo='AIS/16-17/00096' group by ReceiptNo,
S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month ,
Fine, TotalAmount, FHeadId, FHead, Concession, payableAmount) as result

WHERE FEE > 0

这篇关于如何获得大于零值的sqlserver的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！