在 sql select 语句中使用 case

Question

考虑一个具有列金额的表，

Amount
-1235.235
1356.45
-133.25
4565.50
5023
-8791.25

我希望我的结果窗格是这样的，

Debit   Credit
  0     -1235.235
1356.45  0 
  0     -133.25

这是我的存储过程，

USE [HotelBI_CustomDB]
GO
  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_GetJVReport](
@p_FromDate datetime,
@p_ToDate datetime
)
AS
BEGIN
select jv.AccountNo,jv.AccountNoTitle,(select JV_GroupsHead.GroupTitle
    from JV_GroupsHead where JV_GroupsHead.Id=jv.GroupId) as 'GroupName'
,jv.Revenue --" This column i need to check and have to split it into two 
               column "credeit,debit""

from JVFunction1(@p_FromDate,@p_ToDate) as jv

END

如何编写一个选择语句，使其使用像 amount>=0 这样的情况作为信用和 amount <0 作为借方？

Answer 1

根据您的具体需求进行修改

SELECT
   CASE WHEN Amount < 0 THEN ABS(Amount) ELSE NULL AS Debit,
   CASE WHEN Amount >= 0 THEN Amount ELSE NULL AS Credit
FROM
   SomeTable

Answer 2

您的意思是这样，一个单独的列来指示金额的类型吗？

SELECT Amount, CASE WHEN Amount < 0 THEN 'Debit' ELSE 'Credit' END AS Type
FROM SomeTable

Answer 3

我可能会创建一个用户定义的函数来使这更容易一些。

例如。

create function dbo.CreditOrDebit (@amount decimal(9,2), @type char(6))
returns decimal(9,2)
as
begin
declare @output decimal(9,2);
select @output =
    case @type
    when 'debit' then
        case 
            when @amount < 0.00 then ABS(@amount) 
            else 0
        end
    else
        case 
            when @amount >= 0.00 then ABS(@amount) 
            else 0
        end
    end;
    return @output;
end

然后在 Select 语句中使用它，如下所示：

select jv.AccountNo,jv.AccountNoTitle,(select JV_GroupsHead.GroupTitle
    from JV_GroupsHead where JV_GroupsHead.Id=jv.GroupId) as 'GroupName'
,dbo.CreditOrDebit(jv.Revenue,'debit') as debit
,dbo.CreditOrDebit(jv.Revenue,'credit') as credit