编写查询以添加2，其中数字结束0并添加3，其中数字以1结尾。 [英] Write a query to add a 2 where the number ends 0 and add a 3 where the number ends in 1.

问题描述

我的桌子上有一列已售出的行，其中包含流量值的行：



81,10,40,71,21,31,41,30 ，90,61,50,11,20,91,60



我正在尝试编写一个查询来添加一个2，其中数字结束0并添加一个3，数字以1结尾。



我尝试过：



这是我迄今为止的悲伤尝试



选择SalesId，EmployeeId，CAST（以varchar（20）出售）+'1'

来自销售

WHERE出售'％1'



我不知道我是否应该使用CASE或IF语句

I have a table with a column Sold that has rows with the flowing values:

81, 10, 40, 71, 21, 31, 41, 30, 90, 61, 50, 11, 20, 91, 60

I'm trying to write a query to add a 2 where the number ends 0 and add a 3 where the number ends in 1.

What I have tried:

This is my sad attempt so far

Select SalesId, EmployeeId, CAST(Sold as varchar(20)) + '1'
From Sales
WHERE Sold LIKE '%1'

I don't know if I should use CASE or IF statements

推荐答案

尝试

try

select SalesId, EmployeeId, case when right(cast(sold as varchar(20)),1) = '0' then sold + 2 else case when right(cast(sold as varchar(20)),1) = '1' then sold + 1 else sold end end from Sales

除了 RossMW [ ^ ]我建议使用 modulo [ ^ ]。

In addition to solution1 by RossMW[^] i'd suggest to do it in more efficient way by using modulo[^].

DECLARE @tmp TABLE(myNumber INT)

INSERT INTO @tmp (myNumber)
VALUES(81), (10), (40),(71), (21), 
	(31), (41), (30), (90), (61), 
	(50), (11), (20), (91), (60)

SELECT myNumber, CASE WHEN myNumber % 10 = 0 THEN myNumber + 2 ELSE myNumber + 1 END AS NewMyNumber
FROM @tmp 

结果：

Result:

myNumber	NewMyNumber
81			82
10			12
40			42
71			72
21			22
31			32
41			42
30			32
90			92
61			62
50			52
11			12
20			22
91			92
60			62

这篇关于编写查询以添加2，其中数字结束0并添加3，其中数字以1结尾。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！