如何计算两行的差异 [英] How to calculate diiference for two rows

问题描述

下午好。每个人都有。我有桌子

<前lang =文字> id名字
100 Ramu
104 Pradeep
108 Sai
112 Balu

i想要计算id列的差值（104-100 = 4,108-104 = 4）i，ei必须显示此客户端的输出

<前lang =text> id区别名称
100 0 Ramu
104 4 Pradeep
108 4 Sai
112 4 Balu

任何人都可以告诉你怎么做...



感谢你

解决方案

  CREATE  表 #TEMP（ID  INT ，NAME  VARCHAR （ 100 ））
  INSERT   INTO  #TEMP 
  SELECT  '  100'，'  Ramu' 
  UNION  
  SELECT  '  104'，'  Pradeep' 
  UNION  
  SELECT  '  108' ，'  Sai' 
  UNION  
  SELECT  '  112 '，'  Balu' 
 
  CREATE   TABLE  #TEMP_1（ID1  INT   IDENTITY ，ID  INT ，DIFF  INT ，NAME  VARCHAR （ 100 ））
  INSERT   INTO  #TEMP_1（ID，NAME） 
  SELECT  *  FROM  #TEMP 
  DECLARE   @ C   INT ， @ I   INT ， @ D   INT ， @ D1   INT ， @ D2   INT  
  SELECT  @ C = COUNT（*） FROM ＃TEMP_1 
  SET  @ I = 1 
  WHILE  （@ I< = @ C）
  BEGIN  
  IF （@ I％2 = 1）  -  -   原帖说IF（@I = 1）修正从评论中添加 
  BEGIN  
  SET  @ D = 0 
  END  
  ELSE  
  BEGIN  
 
  SELECT  @ D1 = ID  FROM  #TEMP_1  WHERE  ID1 = @ I 
 < span class =code-keyword> SELECT  @ D2 = ID  FROM  #TEMP_1  WHERE  ID1 = @ I-1 
  SET  @ D = @ D1- @ D2 
  END  
   -    SELECT @D  
 更新 A 
  SET  DIFF = @ D 
  FROM  #TEMP_1 A 
  WHERE  ID1 = @ I 
 
  SET  @ I = @ I + 1 
 结束 
 
  SELECT  *  FROM  #TEMP_1 
  DROP  表 #TEMP 
  DROP  表 #TEMP_1 

hi good afternoon every one.i have table

id  name
100 Ramu
104 Pradeep
108 Sai
112 Balu

i want to calculate difference(104-100=4,108-104=4) for id columns i,e i have to display output like this client

id   difference  name
100 0   Ramu
104 4   Pradeep
108 4   Sai
112 4   Balu

can any one please tell how to do this..

thanking you

解决方案

CREATE TABLE #TEMP(ID INT,NAME VARCHAR(100))
INSERT INTO #TEMP
SELECT '100','Ramu'
UNION
SELECT '104','Pradeep'
UNION
SELECT '108','Sai'
UNION
SELECT '112','Balu'

CREATE TABLE #TEMP_1(ID1 INT IDENTITY,ID INT,DIFF INT,NAME VARCHAR(100))
INSERT INTO #TEMP_1(ID,NAME)
SELECT * FROM #TEMP
DECLARE @C INT,@I INT,@D INT,@D1 INT,@D2 INT
SELECT @C=COUNT(*) FROM #TEMP_1
SET @I=1
WHILE(@I<=@C)
BEGIN
	IF(@I%2 =1)   -- original post said IF(@I =1) Fix added from comment
	BEGIN
		SET @D=0
	END
	ELSE
	BEGIN
		
		SELECT @D1=ID FROM #TEMP_1 WHERE ID1=@I
		SELECT @D2=ID FROM #TEMP_1 WHERE ID1=@I-1
		SET @D=@D1-@D2
	END
	--SELECT @D
	update A
	SET DIFF=@D
	FROM #TEMP_1 A
	WHERE ID1=@I
	
	SET @I=@I+1
END

SELECT * FROM #TEMP_1
DROP TABLE #TEMP
DROP TABLE #TEMP_1

这篇关于如何计算两行的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！