在SQL Server中,如何用语句实现：查询某字段的本行记录值如果等于上一行记录值，则本行值+1？

2012-11-11 12:31:41 394 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

合并多条记录的同一字段值SQL语句 1.生成测试数据： create table team(name varchar(32),person varchar(32)); insert into team values('A','Tom'); insert into team values('A','Jim'); insert into team values('A','Anny'); insert into team val 阅读详情

表Table中有如下2列数据：
ID 记录值
-------------------------
1 3000
2 3200
3 3500
4 3500
5 3700
6 3900
7 3900
8 3900
9 4100

返回结果：

ID 记录值
-------------------------
1 3000
2 3200
3 3500
4 3501
5 3700
6 3901
7 3902
8 3903
9 4100

...全文

SQL Server中查询一个值出现的所有表。 Create Procedure sp_Search(@Val Varchar(4000)) As Begin Declare @TBName Varchar(200) Declare @ColName Varchar(200) Declare @Rst int Declare @Sql NVarchar(4000) Declare @TB Table(TbName Varchar(20 阅读详情

chiclyer

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

發糞塗牆 2012-11-12

打赏
举报

按照你的说法，第三行应该是3501，第四行是3502

/*
 CREATE TABLE test (id INT ,记录值 varchar(10) )
 INSERT INTO dbo.test
 SELECT 1, 3000 UNION ALL
 SELECT 2, 3200 UNION ALL
 SELECT 3, 3500 UNION ALL
 SELECT 4, 3500 UNION ALL
 SELECT 5, 3700 UNION ALL
 SELECT 6, 3900 UNION ALL
 SELECT 7, 3900 UNION ALL
 SELECT 8, 3900 UNION ALL
 SELECT 9, 4100
 */
 
 --不相等的值
 SELECT * FROM test a WHERE EXISTS(SELECT 1 FROM (SELECT 记录值 FROM test GROUP BY 记录值 HAVING COUNT(1)<2) b WHERE a.记录值=b.记录值)
 UNION ALL 
 --有相同的值
 SELECT id,LEFT(记录值,3)+CONVERT(VARCHAR(10),ROW_NUMBER()OVER (PARTITION BY 记录值 ORDER BY id) )
 FROM test a WHERE EXISTS(SELECT 1 FROM (SELECT 记录值 FROM test GROUP BY 记录值 HAVING COUNT(1)>1) b WHERE a.记录值=b.记录值)
 ORDER BY id
 
 /*
 id          记录值
 ----------- ----------------
 1           3000
 2           3200
 3           3501
 4           3502
 5           3700
 6           3901
 7           3902
 8           3903
 9           4100
 
 (9 行受影响)
 
 */

javatemptation 2012-11-12

打赏
举报

猜测楼主的数据如下： 1 3000 2 3200 3 3500 4 3500 5 3900 6 3900 7 3900 8 3900 9 4100


USE tempdb;
/*
CREATE TABLE t1
(
	id INT NOT NULL,
	result int NOT NULL
);
INSERT INTO t1(id,result) VALUES
(1,3000),(2,3200),(3,3500),(4,3500),(5,3900),(6,3900),(7,3900),(8,3900),(9,4100);
*/
SELECT * FROM t1;

SELECT 
	t2.id,(t2.result + t2.number) AS result
FROM
(
	SELECT 
	*,ROW_NUMBER() OVER(PARTITION BY t1.result ORDER BY t1.id) - 1 AS number
	FROM t1
) AS t2

生成的效果如下： 1 3000 2 3200 3 3500 4 3501 5 3900 6 3901 7 3902 8 3903 9 4100

唐诗三百首 2012-11-11

打赏
举报

原5,6行,3700不等于3900,为何结果是 ID 记录值 ---------- 5 3700 6 3901

坚_持 2012-11-11

打赏
举报

declare @ta table(ID int,记录值 int)
insert @ta select 1,3000 union all select 2,3200 union all select 3,3500 union all select 4,3500 union all
select 5,3700 union all select 6,3900 union all select 7,3900 union all select 8,3900 union all
select 9,3900 union all select 10,3900 union all select 11,3900 union all select 12,4100
DECLARE @N INT,@I1 INT
SET @N = 1
set @I1 = 1
WHILE @N<(SELECT MAX(ID) FROM @ta)
BEGIN
while @N+1<(SELECT MAX(ID) FROM @ta)
begin
IF (SELECT 记录值 FROM @ta where ID = @N)=(SELECT 记录值 FROM @ta where ID = @N+1)
begin
update @ta set 记录值=记录值+@I1 from @ta where ID = @N+1
end
set @N = @N+1
IF (SELECT 记录值-@I1 FROM @ta where ID = @N)=(SELECT 记录值 FROM @ta where ID = @N+1)
begin
set @I1 = @I1+1
update @ta set 记录值=记录值+@I1 from @ta where ID = @N +1
end
end
set @I1 = 1 set @N = @N+1
END
select * from @ta

kensouterry1 2012-11-11

打赏
举报


;WITH c1(ID, 记录值)
AS
(
SELECT 1, 3000 UNION ALL
SELECT 2, 3200 UNION ALL
SELECT 3, 3500 UNION ALL
SELECT 4, 3500 UNION ALL
SELECT 5, 3700 UNION ALL
SELECT 6, 3900 UNION ALL
SELECT 7, 3900 UNION ALL
SELECT 8, 3900 UNION ALL
SELECT 9, 4100
)
----start query
,c2 AS
(
SELECT
	b.ID,
	CASE WHEN a.记录值 = b.记录值 THEN b.记录值+1 ELSE b.记录值 END 记录值
FROM c1 a
LEFT JOIN c1 b ON a.ID = b.ID - 1 --(b大)
UNION ALL
SELECT TOP 1
	ID,
	记录值
FROM c1
)
SELECT *
FROM c2
ORDER BY ID

--测试结果：
ID          记录值
----------- -----------
NULL        NULL
1           3000
2           3200
3           3500
4           3501
5           3700
6           3900
7           3901
8           3901
9           4100

(10 row(s) affected)

longai123 2012-11-11

打赏
举报

update tb 记录值=记录值+1 where id=(select id from tb as a where a.id=id-1 and a.记录值=记录值)

newtee 2012-11-11

打赏
举报

帮顶下

在SQL Server中,如何用语句实现：查询某字段的本行记录值如果等于上一行记录值，则本行值+1？

相关推荐