-- 建立测试表
CREATE TABLE #T(A CHAR(1),B INT,C INT)-- 插入测试数据
INSERT INTO #T
SELECT 'a',2,1
UNION ALL SELECT 'a',2,2
UNION ALL SELECT 'a',5,3
UNION ALL SELECT 'a',2,4
UNION ALL SELECT 'b',2,5
UNION ALL SELECT 'b',6,6
UNION ALL SELECT 'b',6,7
UNION ALL SELECT 'b',3,8
UNION ALL SELECT 'b',2,9SELECT * FROM #T-- 将数据导入另外一临时表中
SELECT IDENTITY(INT,1,1) ID,A,B,NULL C INTO #TT FROM #T-- 数据处理开始
DECLARE @N TINYINT
SET @N = 0
UPDATE A SET
B = B + @N,
@N = CASE WHEN EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID - 1 AND A = A.A AND B = A.B) THEN @N + B
ELSE 0
END,
C = CASE WHEN NOT EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID + 1 AND A = A.A AND B = A.B) THEN 0
END
FROM #TT A-- 查询出最后经过
SELECT A,B FROM #TT WHERE C = 0-- 删除测试数据
DROP TABLE #T,#TT/*
A B C
---- ----------- -----------
a 2 1
a 2 2
a 5 3
a 2 4
b 2 5
b 6 6
b 6 7
b 3 8
b 2 9A B
---- -----------
a 4
a 5
a 2
b 2
b 12
b 3
b 2(所影响的行数为 7 行)
*/
CREATE TABLE #T(A CHAR(1),B INT,C INT)-- 插入测试数据
INSERT INTO #T
SELECT 'a',2,1
UNION ALL SELECT 'a',2,2
UNION ALL SELECT 'a',5,3
UNION ALL SELECT 'a',2,4
UNION ALL SELECT 'b',2,5
UNION ALL SELECT 'b',6,6
UNION ALL SELECT 'b',6,7
UNION ALL SELECT 'b',3,8
UNION ALL SELECT 'b',2,9SELECT * FROM #T-- 将数据导入另外一临时表中
SELECT IDENTITY(INT,1,1) ID,A,B,NULL C INTO #TT FROM #T-- 数据处理开始
DECLARE @N TINYINT
SET @N = 0
UPDATE A SET
B = B + @N,
@N = CASE WHEN EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID - 1 AND A = A.A AND B = A.B) THEN @N + B
ELSE 0
END,
C = CASE WHEN NOT EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID + 1 AND A = A.A AND B = A.B) THEN 0
END
FROM #TT A-- 查询出最后经过
SELECT A,B FROM #TT WHERE C = 0-- 删除测试数据
DROP TABLE #T,#TT/*
A B C
---- ----------- -----------
a 2 1
a 2 2
a 5 3
a 2 4
b 2 5
b 6 6
b 6 7
b 3 8
b 2 9A B
---- -----------
a 4
a 5
a 2
b 2
b 12
b 3
b 2(所影响的行数为 7 行)
*/
解决方案 »
- varchar 表示最大儲存體大小的問題
- 求 SQL2000 如何存储图片
- 根据一个select后的结果,取出另外一个结果。不用游标如何实现?
- 共现次数的效率问题---两周一直没解决
- 请问一个SQL Server查询方式问题
- 重复数据删除问题
- 关于case语句,各位大虾指教
- 我使用的数据库为SQL SERVER2K,但如何将我的JSP程序放在别的机器上运行呢?急!!一定给分!
- 请问怎样锁定SQL中的一张表。
- 象高手请教:
- 怎么写SQL能实现且效率会高?
- select * from tablename where datetime = @dt 在数据库中datetime是年月日时分秒 想实现如下效果
CREATE TABLE #T(A CHAR(1),B INT,C INT)-- 插入测试数据
INSERT #T
SELECT 'a',2,1
UNION ALL SELECT 'a',2,2
UNION ALL SELECT 'a',2,3
UNION ALL SELECT 'a',5,4
UNION ALL SELECT 'a',2,5
UNION ALL SELECT 'b',2,6
UNION ALL SELECT 'b',6,7
UNION ALL SELECT 'b',6,8
UNION ALL SELECT 'b',6,9
UNION ALL SELECT 'c',10,10
UNION ALL SELECT 'c',10,11
UNION ALL SELECT 'c',2,12
UNION ALL SELECT 'e',6,13SELECT * FROM #T-- 将数据导入另外一临时表中
SELECT IDENTITY(INT,1,1) ID,A,B,NULL C INTO #TT FROM #T-- 数据处理开始
DECLARE @N TINYINT
SET @N = 0
UPDATE A SET
B = B + @N,
@N = CASE WHEN EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID - 1 AND A = A.A AND B = A.B) THEN @N + B
ELSE 0
END,
C = CASE WHEN NOT EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID + 1 AND A = A.A AND B = A.B) THEN 0
END
FROM #TT A-- 查询出最后经过
SELECT A,B FROM #TT WHERE C = 0-- 删除测试数据
DROP TABLE #T,#TT/*A B C
---- ----------- -----------
a 2 1
a 2 2
a 2 3
a 5 4
a 2 5
b 2 6
b 6 7
b 6 8
b 6 9
c 10 10
c 10 11
c 2 12
e 6 13(所影响的行数为 13 行)
A B
---- -----------
a 6
a 5
a 2
b 2
b 18
c 20
c 2
e 6(所影响的行数为 8 行)*/
SELECT IDENTITY(INT,1,1) ID,A,B,NULL C INTO #TT FROM 你的表-- 数据处理开始
DECLARE @N TINYINT
SET @N = 0
UPDATE A SET
B = B + @N,
@N = CASE WHEN EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID - 1 AND A = A.A AND B = A.B) THEN @N + B
ELSE 0
END,
C = CASE WHEN NOT EXISTS (SELECT 1 FROM #TT WHERE ID = A.ID + 1 AND A = A.A AND B = A.B) THEN 0
END
FROM #TT A-- 查询出最后经过
SELECT A,B FROM #TT WHERE C = 0-- 删除测试数据
DROP TABLE #TT
insert into #1 values('a',2,1)
insert into #1 values('a',2,2)
insert into #1 values('a',5,3)
insert into #1 values('a',2,4)
insert into #1 values('b',2,5)
insert into #1 values('b',6,6)
insert into #1 values('b',6,7)
insert into #1 values('b',3,8)
insert into #1 values('b',2,9)select * into #2 from #1update a
set c=b.c
from #2 a,#2 b
where a.a=b.a and a.b=b.b and a.c=b.c+1select a,sum(b) b from #2 group by a,c
a b
----- -----------
a 4
a 5
a 2
b 2
b 12
b 3
b 2(7 row(s) affected)
??是啊,是有点复杂化了,我看了你的,你的现在的解法只支持
A B C
---- ----------- -----------
a 2 1
a 2 2
a 5 3
如果是
A B C
---- ----------- -----------
a 2 1
a 2 2
a 2 3
a 2 4
a 2 5
a 5 6又应该如何处理呢。
select a.a
,b=a.b+isnull(b.b,0)
,c=(select sum(1) from 表 aa where c<=a.c
and not exists(select 1 from 表 where a=aa.a and b=aa.b and c=aa.c-1))
from 表 a left join(
select a,b=sum(b),c=min(c)-1 from 表 a
where exists(select 1 from 表 where a=a.a and b=a.b and c=a.c-1)
group by a,b
) b on a.c=b.c
where not exists(select 1 from 表 where a=a.a and b=a.b and c=a.c-1)
declare @t TABLE(A CHAR(1),B INT,C INT)
INSERT INTO @t
SELECT 'a',2,1
UNION ALL SELECT 'a',2,2
UNION ALL SELECT 'a',2,3
UNION ALL SELECT 'a',5,4
UNION ALL SELECT 'a',2,5
UNION ALL SELECT 'b',2,6
UNION ALL SELECT 'b',6,7
UNION ALL SELECT 'b',6,8
UNION ALL SELECT 'b',3,9
UNION ALL SELECT 'b',2,10--查询处理
select a.a
,b=a.b+isnull(b.b,0)
,c=(select sum(1) from @t aa where c<=a.c
and not exists(select 1 from @t where a=aa.a and b=aa.b and c=aa.c-1))
from @t a left join(
select a,b=sum(b),c=min(c)-1 from @t a
where exists(select 1 from @t where a=a.a and b=a.b and c=a.c-1)
group by a,b
) b on a.c=b.c
where not exists(select 1 from @t where a=a.a and b=a.b and c=a.c-1)/*--测试结果
a b c
---- ----------- -----------
a 6 1
a 5 2
a 2 3
b 2 4
b 12 5
b 3 6
b 2 7(所影响的行数为 7 行)
--*/
仔细看看坐下来学习学习。
(a int null,
b int null,
c int null)
go
declare @c int
, @a int
, @b int
, @max int
, @b1 int
set @c=1
set @max=select max(c) from table1)
while @c<>0
begin
set @a=(select a from table1 where c=@c)
set @b=(select b from table1 where c=@c)
if (@max<=@c+1)
begin
set @b1=(select b from tbale1 where c=@c+1)
if @b=@b1
begin
insert into table2(a,b,c)
values (@a,@b,@c)
set @c=@c+2
end
if @b<>@b1
begin
insert into table2(a,b,c)
values(@a,@b,@c)
set @c=@c+1
end
end
else
set @c=0
end
select * from table2
CREATE TABLE #T(A CHAR(1),B INT,C INT,D varchar(2))
INSERT #T(A,B,C)
SELECT 'a',2,1
UNION ALL SELECT 'a',2,2
UNION ALL SELECT 'a',2,3
UNION ALL SELECT 'a',5,4
UNION ALL SELECT 'a',2,5
UNION ALL SELECT 'b',2,6
UNION ALL SELECT 'b',6,7
UNION ALL SELECT 'b',6,8
UNION ALL SELECT 'b',6,9
UNION ALL SELECT 'c',10,10
UNION ALL SELECT 'c',10,11
UNION ALL SELECT 'c',2,12
UNION ALL SELECT 'e',6,13
go
declare @count int
set @count=0
update #t
set D=@count,@count=@count+1
where not exists(select 1 from #t t where #t.C=t.C-1 and #t.A=t.A and #t.B=t.B)
Go
update #t
set D=(select min(D) from #t t where t.C>#t.c) where #t.D is null
select A,sum(B) B from #t group by A,Ddrop table #t
from #2 a,#2 b
where a.a=b.a and a.b=b.b and a.c=b.c+1)
update a
set c=b.c
from #2 a,#2 b
where a.a=b.a and a.b=b.b and a.c=b.c+1如果是
A B C
---- ----------- -----------
a 2 1
a 2 2
a 2 3
a 2 4
a 2 5
a 5 6
结果:a b
----- -----------
a 10
b 5(2 row(s) affected)
select a,sum(b) b from #2 group by a,c