如何在SQL Server中使用选择大小写 [英] how to use select case in sql server

问题描述

使用下面的代码获取支付现金和使用卡支付的客户的详细信息，
使用这个我可以显示它是现金还是卡.但是客户需要如果有卡，则需要出示卡号和银行名称.，我被打到这里了，请帮助我.，提前谢谢.，

using below code getting details of customer who paid cash and who paid using card,
using this i can show whether its cash or card. but client need if card then need to show card no and bank name., i am getting struck here please help me out., thanx in advance.,

DELCARE @TEMPTABLE TABLE
(
CUSNAME NVARCHAR(450),
PAYMENT_MODE nvarchar(250),
CARDNO nvarchar(450),
BANKNAME nvarchar(450),
)

INSERT INTO @TEMPTABLE
(
CUSNAME,PAYMENT_MODE,CARDNO,BANKNAME
)

SELECT CUSNAME,

CASE (PAYMENTMODE)
WHEN 1 THEN 'CASH'

   --here i dont want to show anything 

WHEN 2 THEN 'CARD'
     --here i have to display cardno and bankname [store into @temptable]

ELSE 'UNKNOWN'

END

FROM PAYMENT_DETAILS 


SELECT * FROM  @TEMPTABLE

推荐答案

尝试:

选择CUSNAME，CASE(PAYMENTMODE)
当1 THEN``现金''
当2 THEN''CARD''+ CARDNO +''''+ BANKNAME
ELSE``未知''
END
FROM PAYMENT_DETAILS

Try :

SELECT CUSNAME, CASE (PAYMENTMODE)
WHEN 1 THEN ''CASH''
WHEN 2 THEN ''CARD''+CARDNO+'' ''+BANKNAME
ELSE ''UNKNOWN''
END
FROM PAYMENT_DETAILS

这里有更优化的解决方案

Here more optimized solution

INSERT INTO @TEMPTABLE
(
	CUSNAME,PAYMENT_MODE,CARDNO,BANKNAME
)
 
SELECT CUSNAME,

PAYMENTMODE,

CASE (PAYMENTMODE)
WHEN 1 THEN ''
WHEN 2 THEN PAYMENT_DETAILS.CARDNO
ELSE 'UNKNOWN'
END,

CASE (PAYMENTMODE)
WHEN 1 THEN ''
WHEN 2 THEN PAYMENT_DETAILS.BANKNAME
ELSE 'UNKNOWN'
END

FROM PAYMENT_DETAILS

尝试此

TRY THIS

DECLARE @TEMPTABLE TABLE
(
CUSNAME NVARCHAR(450),
PAYMENT_MODE nvarchar(250),
CARDNO nvarchar(450),
BANKNAME nvarchar(450)
)

INSERT INTO @TEMPTABLE
(
CUSNAME,PAYMENT_MODE,CARDNO,BANKNAME
)
SELECT 
PAYMENT_DETAILS.CUSNAME,
'CASH',
'',
''
FROM PAYMENT_DETAILS where PAYMENTMODE = 'CASH'


INSERT INTO @TEMPTABLE
(
	CUSNAME,PAYMENT_MODE,CARDNO,BANKNAME
)
SELECT 
PAYMENT_DETAILS.CUSNAME,
'CARD',
PAYMENT_DETAILS.CARDNO,
PAYMENT_DETAILS.BANKNAME
 
FROM PAYMENT_DETAILS where PAYMENTMODE = 'CARD'


INSERT INTO @TEMPTABLE
(
	CUSNAME,PAYMENT_MODE,CARDNO,BANKNAME
)
SELECT 
PAYMENT_DETAILS.CUSNAME,
'UNKNOWN',
'',
''
 
FROM PAYMENT_DETAILS where PAYMENTMODE != 'CARD' AND PAYMENTMODE != 'CASH'

SELECT * FROM @TEMPTABLE

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