22,209
社区成员
发帖
与我相关
我的任务
分享
/*
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 行受影响)
*/
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
;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)