sql如何判断相同的值但处于不同列的个数
发布网友
发布时间：2024-10-21 21:41
共2个回答
热心网友
时间：2024-11-09 09:05
declare @t table(A1 int ,B1 varchar(20),C1 varchar(20),D1 varchar(20))
declare @tb table(id int,val varchar(255),col varchar(255))
insert into @t
select 3, 'c' , 'd' , 'e'
union all
select 5 , 'd' , 'e' , 'c'
union all
select 6 , 'w' , 'c' , 'd'
union all
select 4 , 'c' , 'w' , 'd'
union all
select 8 , 'a' , 'w' , 'd'
--end of 构造测试数据
--(1)把原表@t添加流水号列插入临时表#t
select id = IDENTITY(int,1,1),*
INTO #t
from @t
--(2)把临时表#t中B1,C1,D1的各种组合加上流水号插入临时表@tb
insert into @tb
select id,B1+C1+D1,null
from #t
union all
select id,B1+D1+C1,null
from #t
union all
select id,C1+B1+D1,null
from #t
union all
select id,C1+D1+B1,null
from #t
union all
select id,D1+C1+B1,null
from #t
union all
select id,D1+B1+C1,null
from #t
--(3)把相同ID的各组合统一用最大的那个组合插入表@tb的列col中
update t
set col = x.skey
from (select id,max(val) as skey
from @tb
group by id
) x right join @tb t on x.id = t.id and val = skey
--(4)把更新后的@tb与原表@t关联然后分组,得到最终结果
select col as 相同数据,sum(A1) as 合计A1
from(select b.*,a.col
from @tb a inner join #t b
on a.id = b.id
where col is not null
) t
group by col
--删除临时表
DROP table #T
---当然也可以用游标,用游标思路就简单多了,每读取一条记录就和全部数据对比,不过可能效率的问题,这里没有用游标
热心网友
时间：2024-11-09 09:03
(select sum(A1) A1,B1,C1,D1 from table1
group by B1,C1,D1) a,table1 b
where a.B=b.B1 and a.C1=b.C1 and a.D1=b.D1
