34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT A.abc,B.def,isnull((SELECT TOP 1 ghi FROM C WHERE C.jkl=B.mno),'') AS pqr
FROM A INNER JOIN B ON A.stu=B.vwx
SELECT A.abc,B.def,isnull((SELECT TOP 1 ghi FROM C WHERE C.jkl=B.mno),'') AS pqr
FROM A INNER JOIN B ON A.stu=B.vwx
SELECT A.abc,B.def,isnull(C2.ghi2,'') AS pqr
FROM A
INNER JOIN B ON A.stu=B.vwx
left join (SELECT jkl,min(ghi) ghi2 FROM C group by jkl) c2 on C2.jkl=B.mno
with cte as
(
select ghi,jkl,rn=ROW_NUMBER() over(PARTITION by jkl order by getdate() )
from c
)
select A.abc,B.def,ck.
from A INNER JOIN B ON A.stu=B.vwx
left join cte c on a.jkl=b.mno
where rn=1
--使用case when then end代替isnull,以前曾试过。case...比isnull的效率要高一些。数据越多越明显。
select abc,def,pqr=case when pqr is null then '' else pqr end
from
(SELECT A.abc,B.def,isnull((SELECT TOP 1 ghi FROM C WHERE C.jkl=B.mno),'') AS pqr
FROM A INNER JOIN B ON A.stu=B.vwx)a
或者直接在程序中去转换。减少数据库处理压力。。。