--sql 2008以上的 WITH cte AS ( SELECT t1.djbh,t1.ph,t2.mc,t2.je,ROW_NUMBER() OVER(PARTITION BY t1.djbh,t2.mc,t2.je ORDER BY t2.djbh) rn FROM csdnhzb t1 INNER JOIN csdnmxb t2 ON t1.djbh=t2.djbh ) SELECT djbh,ph,mc,je FROM cte WHERE rn=1
select a.djbh,a.ph,b.mc,b.je from (select djbh,ph,row_number() over(partition by djbh order by getdate()) 'rn' from csdnhzb) a inner join (select djbh,mc,je,row_number() over(partition by djbh order by getdate()) 'rn' from csdnmxb) b on a.djbh=b.djbh and a.rn=b.rn
--sql 2008以上的
WITH cte
AS
(
SELECT t1.djbh,t1.ph,t2.mc,t2.je,ROW_NUMBER() OVER(PARTITION BY t1.djbh,t2.mc,t2.je ORDER BY t2.djbh) rn
FROM csdnhzb t1 INNER JOIN csdnmxb t2 ON t1.djbh=t2.djbh
)
SELECT djbh,ph,mc,je FROM cte WHERE rn=1
select a.djbh,a.ph,b.mc,b.je
from
(select djbh,ph,row_number() over(partition by djbh order by getdate()) 'rn'
from csdnhzb) a
inner join
(select djbh,mc,je,row_number() over(partition by djbh order by getdate()) 'rn'
from csdnmxb) b on a.djbh=b.djbh and a.rn=b.rn
/*
djbh ph mc je
---------- ---------- ---------- --------------
dj123 ph001 第一个名称 100.00
dj123 ph002 第一个名称 200.00
dj123 ph003 第一个名称 300.00(3 row(s) affected)
*/