如何在SQL中使用case语句 [英] How can use case statement in SQL

问题描述

在每一个我尝试获得那些余额金额为0且余额金额大于0的记录，即余额> 0为'统计'

如果余额金额为0则其显示其统计数据是'付费'，如果余额大于0，那么它显示的数据为'痛苦'

请任何人告诉我我该怎么做

我尝试这个但是失败得到我想要的结果



我尝试过：

hi every one i try to get those records which have balance amount is 0 and Balance amount is greater then 0 i.e balance>0 as 'Stats'
if balance amount is 0 then its show its stats is 'Paid' if balance amount greater then 0 then it show stats as 'Painding'
please any one tell me how can i do this
I try this but fail to get result as i want

What I have tried:

SELECT [InvoiceNo]
      ,[Date]      
      ,[CustmID]    
      ,[TotalDue]	   
     ,case Balance when Balance>0 then 'Pending'  when  Balance==0 then  'Paid'	  	   
  FROM [dbo].[Sale_Bill_details]

推荐答案

你有没有意图使用 TotalDue 而不是Balance？这会将您的语句更改为：

Could you have possible meant to use the TotalDue instead of Balance? This would change your statement to look like this:

SELECT [InvoiceNo], [Date], [CustmID], [TotalDue]
,  [Status] = CASE
		WHEN [TotalDue] >0 THEN 'Pending'
		WHEN [TotalDue] =0 THEN 'Paid'	  	   
END

FROM [dbo].[Sale_Bill_details]

但是;我对此有一个问题，因为它不能弥补多付并且负余额的人。您应该使用ELSE语句或者使用ELSE更改第二行：

However; I have a problem with this, as it does not compensate for someone who overpaid and has a negative balance. You should be using an ELSE statement OR alternatively change the second line:

-- using ELSE
SELECT [InvoiceNo], [Date], [CustmID], [TotalDue]
,  [Status] = CASE
		WHEN [TotalDue] > 0 THEN 'Pending'
		WHEN [TotalDue] < 0 THEN 'Paid'
        ELSE                     'Credit Due'
END

FROM [dbo].[Sale_Bill_details]

-- alternative 
SELECT [InvoiceNo], [Date], [CustmID], [TotalDue]
,  [Status] = CASE
		WHEN [TotalDue] > 0 THEN 'Pending'
		WHEN [TotalDue] <=0 THEN 'Paid'	  	   
END

FROM [dbo].[Sale_Bill_details]

但我仍有问题 - 因为[状态]（待定，付费，信用到期）是一个演示项目;并且通常不应该在数据库中保存或计算。

对我的分离表示DB用于显示数据。应用程序和它的朋友结束返回数据，并选择从中显示的内容。



附录由于错误检查代码后我向你指出，我在你的CASE声明中发现了两个错误1。如果在开始语句中定义条件，则不能在WHEN语句中使用

2. TSQL使用单个等号来表示相等和赋值，第二个WHEN使用==

3. CASE语句没有END。

But I still have problems with this- as [Status] ("Pending", "Paid", "Credit Due") is a presentation item; and generally should neither be saved nor calculated within the database.
Separation of Concerns to me says that the DB is used to display the data. The application and it's friend end return the data, and choose what to display from it.

Addendum After reviewing the code due to an error pointed out to me, I found two errors in your CASE statement1. If you define the condition in the opening statement, it cannot be used in the WHEN statement
2. TSQL uses a single equals sign for equality and assignment, your second WHEN uses ==
3. There is no END to the CASE statement.

,	case Balance when Balance >0 then 'Pending'  when Balance == 0 then  'Paid'
-- above line will error out. SQL Studio and Sql Ops Studio will flag the errors
,	case         when Balance >0 then 'Pending'  when Balance =  0 then  'Paid' END

概念验证，将我推荐的CASE声明与原始声明（需要注释掉）和简单的修复结合起来错误的陈述

Proof of Concept along, incorporating my recommended CASE statement along with the original one (will need to be commented out) and the simple fix to the bad statement

DECLARE @Sample TABLE (InvoiceNo INT NULL, [Date] DATE NULL, Custom ID INT NULL, TotalDue MONEY NULL, Balance MONEY NULL)

INSERT @Sample
VALUES (1, GetDate(), 1, 123, 123)
,      (2, GetDate(), 2, 123, 0)
,      (3, GetDate(), 3, 123, -123)
,      (4, GetDate(), 4, 0, null )

SELECT [InvoiceNo]
      ,[Date]      
      ,[CustomID]    
      ,[TotalDue]	   
     , [Status] = case
		when Balance > 0 then 'Pending'
		when Balance = 0 then 'Paid'
		when Balance < 0 then 'Credit Due'
		else                  'na' 
	END

,	case Balance when Balance >0 then 'Pending'  when Balance == 0 then  'Paid'
,	case         when Balance >0 then 'Pending'  when Balance =  0 then  'Paid' END
FROM @Sample

运行此项（错误的行已注释掉）将返回类似这：

Running this (with the bad line commented out) will return something like this:

InvoiceNo	Date	    CustomID	TotalDue	Status       (No column name)
1	        2018-11-02	1	          123.00    Pending      Pending
2	        2018-11-02	2	          123.00    Paid         Paid
3	        2018-11-02	3	          123.00    Credit Due   NULL
4	        2018-11-02	4	            0.00    na           NULL

SELECT [InvoiceNo]
      ,[Date]      
      ,[CustmID]    
      ,[TotalDue]	   
     ,  case 
		when Balance>0 then 'Pending'  
		when  Balance=0 then  'Paid'	  	   
	end case
  FROM [dbo].[Sale_Bill_details]

这篇关于如何在SQL中使用case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！