如何从列表中删除所有重复的对？

2024-05-02 • 问答

我想添加所有CY的值，其中每个PID的PY量相同

换句话说：PID 1的PY和PID 4的PY相同。 PID 2的PY和PID 5的PY相同

Select
    [ID],a.[PY],a.[CY],a.[LAT],a.[LON] Into #tempa
        From MyTable as a
            INNER JOIN MyTable as b on a.PY = b.PY and a.ID <> b.ID

Select
    Convert (varchar,LAT) + ',' + convert (varchar,LON) as 'Combined',SUM(CY) as Total,COUNT(ID) as [Count] Into #tempb
        From #tempa
            Group by Convert (varchar,LON)
            Having COUNT(ID) < 2

Select
    SUM(Total) as [Total]
        From #tempb

如何仅添加ID 1、4和5的CY值？

您可以轻松删除第2行和第3行：

select *
from insurance i
where not exists (select 1
                  from insurance i2
                  where i2.location_pair = i.location_pair and i2.pid <> i.pid
                 );

根据您的描述，我认为您想按py进行汇总：

select min(pid),py,sum(cy),min(location_pair)
from insurance i
where not exists (select 1
                  from insurance i2
                  where i2.location_pair = i.location_pair and i2.pid <> i.pid
                 )
group by py;

但是，这不会返回您指定的结果。 Here是db 小提琴。

Select * Into #temp2 From #temp t
Where Exists (
Select 1 From #temp t2
Where i2.PY = t.PY and t2.ID <> t.ID);
Select * Into #temp3 From #temp t
Where Not exists (
Select 1 From #Temp t2
Where t2.ID <> t.ID);

Select * From #temp2
Select * From #temp3

SELECT SUM(a.CY) as CY FROM #temp2 a
INNER JOIN #temp3 b on a.ID = b.ID

如何从列表中删除所有重复的对？

yjc753951 回答：如何从列表中删除所有重复的对？

大家都在问