sql子查询作为表

爱是永恒2023

于 2021-03-20 16:01:03 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/weixin_46942018/article/details/115029307

SQL 专栏收录该内容

16 篇文章

订阅专栏

SELECT A.UCUID,C.No AS StuNO,A.FacePic,ISNULL(A.AllScore,0)AllScore,tab.USDramaCount,tab.DramaID,DramaCount 
--,row_number()over(order by A.AllScore DESC)AS RowNum
FROM 
(SELECT UCUID,FacePic,(Score+ShareScore+(LikeCount+AdminLikeCount)+(CallCount+AdminCallCount)*10) AS AllScore 
FROM TV_UserDubbing)AS A
LEFT JOIN uc_User B ON  A.UCUID=B.UID
LEFT JOIN uc_UserNo C ON C.UID=B.UID
LEFT JOIN (SELECT COUNT(1)AS USDramaCount,UCUID,DramaID FROM TV_UserSoundRecording GROUP BY UCUID,DramaID)tab ON A.UCUID = tab.UCUID
LEFT JOIN (SELECT COUNT(1)AS DramaCount,DramaID from TV_Drama_Detail E WHERE E.Status=1 GROUP BY DramaID)mm ON mm.DramaID = tab.DramaID
WHERE USDramaCount IS NOT NULL AND DramaCount IS NOT NULL AND tab.DramaID IS NOT NULL AND USDramaCount=DramaCount 
ORDER BY A.AllScore DESC