从三列的不同组合中选择所有列

Question

我必须根据三列的不同组合将记录插入到另一个表中。表A有50列，因为我必须取三列的不同值，并将所有列值插入另一个表。我该怎么做？我试着在下面做，但出了错误。

select * from engagementmasterstaging 
where EngagementId 
in (select distinct EngagementId,ServiceLine,SubServiceLine 
from engagementmasterstaging)

Answer 1

如果需要所有列，请使用窗口函数：

select . . .    -- list the columns here
from (select ems.*,
row_number() over (partition by EngagementId, ServiceLine, SubServiceLine order by EngagementId) as seqnum
from engagementmasterstaging ems
) ems
where seqnum = 1; 

如果您在暂存表中有一个主键，您可以使用它，并且仍然使用select *:

select ems.*
from engagementmasterstaging ems
where ems.pk = (select min(ems2.pk)
from engagementmasterstaging ems2
where ems2.EngagementId = ems.EngagementId and
ems2.ServiceLine = ems.ServiceLine and
ems2.SubServiceLine = ems.SubServiceLine
);