在单行中写一个更新语句... [英] write a Update Statement in single Line for...

问题描述

在单行写一个更新声明...



ID名称Sal

1 Ram 1000 ------ >更新+ 5％

2 Shyam 2000 ------>更新-10％

3 John 3000 ------>更新+ 15％

4史密斯4000 ------>更新-20％

Write a Update Statement in single Line for...

ID Name Sal
1 Ram 1000------>Update +5%
2 Shyam 2000------>Update -10%
3 John 3000------>Update +15%
4 Smith 4000------>Update -20%

推荐答案

Create Table #Temp (Id Int,Name Nvarchar(40),Salary Real)
Insert into #Temp 
Select 1,'Ram',1000
Union All Select 2,'Shyam',2000
Union all Select 3,'John',3000
Union All Select 4,'Smith',4000

Update #Temp Set Salary= Case When Id = 1 Then Salary+(Salary*0.05)
			      When Id = 2 Then Salary-(Salary*0.10)
			      When Id = 3 Then Salary+(Salary*0.15)
			      When Id = 4 Then Salary-(Salary*0.20) End
Select * from #Temp
Drop Table #Temp

如果您正在尝试添加id * 5％的工资为奇数和减少ID * 10％为偶数然后查询应该像这样...

If you are trying to add id * 5% of salary for Odd Numbers and Decrease Id*10% for even Numbers Then the Query Should be Like This...

Update #Temp Set Salary= Case When Id % 2 <> 0  Then Salary + ( Salary *Id* 0.05 )
                              Else Salary - ( Salary * Id * 0.05 ) End

试试这个脚本：

Try this script:

DECLARE @tmp TABLE (ID INT IDENTITY(1,1), [Name] VARCHAR(30), Sal INT)
 
INSERT INTO @tmp ([Name], Sal)
SELECT 'Ram', 1000 ------>Update +5%
UNION ALL SELECT 'Shyam', 2000 ------>Update -10%
UNION ALL SELECT 'John', 3000 ------>Update +15%
UNION ALL SELECT 'Smith', 4000 ------>Update -20%

UPDATE t1
	SET t1.Sal = t2.NewSal
FROM @tmp AS t1 INNER JOIN (
	SELECT ID, [Name], Sal, CASE WHEN (RowNo % 2) = 0 THEN Sal+(Sal/100*Inc*RowNo*-1) ELSE Sal+(Sal/100*Inc*RowNo) END AS NewSal
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNo, ID, [Name], Sal, 5 AS Inc
		FROM @tmp
		) AS Src
		) AS t2 ON t1.ID = t2.ID

SELECT *
FROM @tmp

结果：

Result:

ID      Name    Sal
------------------------------
1	Ram	1050
2	Shyam	1800
3	John	3450
4	Smith	3200

注意：我使用 ROW_NUMBER（） functi以避免 ID 字段中断

Note: I used ROW_NUMBER() function to avoid breaks in ID field

这篇关于在单行中写一个更新语句...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！