56,678
社区成员
发帖
与我相关
我的任务
分享
SELECT a.sneid,a.tneid,a.asneid,a.atneid,a.scount+b.scount FROM t AS a
INNER JOIN t AS b on a.sneid=b.sneid and a.tneid=b.tneid and a.asneid=b.atneid and a.atneid=b.asneid
GROUP BY a.sneid,a.tneid
执行结果:
select d.id,d.sneid,d.tneid,d.asneid,d.atneid,d.scount from (select c.*,row_number() over(partition by sneid,tneid) rn from (select a.id,a.sneid,a.tneid,a.asneid,a.atneid,a.scount+b.scount scount from t1 a inner join t1 b on a.sneid=b.sneid and a.tneid=b.tneid and a.asneid=b.atneid and a.atneid=b.asneid) c) d where rn=1
union all
select c.id,c.sneid,c.tneid,c.asneid,c.atneid,c.scount from (select a.id,a.sneid,a.tneid,a.asneid,a.atneid,a.scount, b.id c1 from t1 a left join t1 b on a.sneid=b.sneid and a.tneid=b.tneid and a.asneid=b.atneid and a.atneid=b.asneid)c where c1 is null;
满足要求吗?
select * from (
select a.sneid,a.tneid,a.asneid,a.atneid,a.scount+b.scount as count1,
ROW_NUMBER() over(partition by a.sneid,a.tneid order by getdate()) as n from ysx as a
inner join ysx as b on a.sneid=b.sneid and a.tneid=b.tneid and a.asneid=b.atneid and a.atneid=b.asneid
) a where n=1
select * from (
select a.sneid,a.tneid,a.asneid,a.atneid,a.scount+b.scount as count1,
ROW_NUMBER() over(partition by a.sneid,a.tneid order by getdate()) as n from ysx as a
inner join ysx as b on a.sneid=b.sneid and a.tneid=b.tneid and a.asneid=b.atneid and a.atneid=b.asneid
) a where n=1
两条任意一条都可以,只要把结果sum起来。[/quote]