代码之家 › 专栏 › 技术社区 › ACP

在sqlselect语句中使用case

case select sql-server-2005

1

ACP · 技术社区 · 15 年前

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

如何编写select语句,使其使用amount>=0作为贷方,amount<0作为借方这样的大小写?

3 回复 | 直到 15 年前

1

4

gbn 15 年前

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

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

2

1

AdaTheDev 15 年前

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

3

0

Andrew Carmichael 15 年前

如。

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