表中有上千条记录,字段1和字段2 有部分记录交叉相等
表A
字段1 字段2 字段3
aaa bbb 1
ddd fff 2
bbb aaa 2
aaa cccc 3
针对表中这种交叉相等的记录,如上表,第一行和第四行交叉相等,如何把字段3的值相加并删除这两行其中一行
表A
字段1 字段2 字段3
aaa bbb 1
ddd fff 2
bbb aaa 2
aaa cccc 3
针对表中这种交叉相等的记录,如上表,第一行和第四行交叉相等,如何把字段3的值相加并删除这两行其中一行
(select top 1 (字段2) from 表A as b where a.字段1=b.字段1) as 字段2,
(select sum(字段3) from 表A as b where a.字段1=b.字段1 order by 字段1 asc) as 字段3
from 表A as a
group by 字段1
(select c1,c2,sum(c3) c3 from tb group by c1,c2) a ,
(select c1,c2,sum(c3) c3 from tb group by c1,c2) b
where a.c1 = b.c2 and a.c2 = b.c1 and a.c1>a.c2
select a.c1,a.c2,a.c3+b.c3 from
(select c1,c2,sum(c3) c3 from tb a a.c1>a.c2 where exists(select 1 from tb c1=a.c2 and c2 = a.c1) group by c1,c2) a ,
(select c1,c2,sum(c3) c3 from tb a a.c1<a.c2 where exists(select 1 from tb c1=a.c2 and c2 = a.c1) group by c1,c2) b ,
where a.c1 = b.c2 and a.c2 = b.c1
union
select c1,c2,c3 from tb a where not exists(select 1 from tb c1=a.c2 and c2 = a.c1)
drop table A
go
create table A(字段1 varchar(10),字段2 varchar(10),字段3 int)
insert into A
select 'aaa', 'bbb', 1 union all
select 'ddd', 'fff', 2 union all
select 'bbb', 'aaa', 2 union all
select 'aaa', 'cccc', 3 declare @字段1 varchar(10)
declare cur cursor for select 字段1 from (select 字段1,count(1) as count_i from A group by 字段1 having count(1)>1) a
open cur
fetch next from cur into @字段1
while(@@fetch_status=0)
begin
declare @count int
select @count=sum(字段3) from A where 字段1=@字段1
declare @字段2 varchar(10)
select top 1 @字段2=字段2 from A where 字段1=@字段1
delete from A where 字段1=@字段1
insert into A(字段1,字段2,字段3) values(@字段1,@字段2,@count)
fetch next from cur into @字段1
end
close cur
deallocate cur
select * from A
--
绝对正确
嗷嗷接分
[/align]
FROM relation1 AS a
INNER JOIN relation1 AS b
ON a.c1 = b.c2 and a.c2 = b.c1
WHERE a.c1>a.c2;[align=center]==== 思想重于技巧 ====
[/align]