mysql - 连接具有不同行和多个选择语句的两个表

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center。
                            
                        
                    
                
                                7年前关闭。
            
                    
我有一个结果

select D.name as username, E.maxViews AS EmaxViews from (select A.video_id, A.uploaded_by AS username,max(B.numViews) AS maxViews,count(*) AS C from Videos A, (select video,count(*) AS numViews from Views GROUP BY video) B where A.video_id=B.video GROUP BY A.uploaded_by) E, Users D where D.username=E.username

这给了我列为username,EmaxViews并且给了我93行

我还有另一个表Users (username,date_registered,name)，其中有100个查询

我想将上述两个查询作为一个表加入

username, date_registered, EmaxViews

我尝试过这样

select name, date_registered,G.EmaxViews from Users F LEFT OUTER JOIN (select D.name as username, E.maxViews AS EmaxViews from (select A.video_id, A.uploaded_by AS username,max(B.numViews) AS maxViews,count(*) AS C from Videos A, (select video,count(*) AS numViews from Views GROUP BY video) B where A.video_id=B.video GROUP BY A.uploaded_by) E, Users D where D.username=E.username) G ON F.username=G.username ORDER BY F.date_registered;

现在，我将获得100行以及name和date_registered的适当值，但所有行的EmaxViews都将为NULL。

我的查询出了什么问题？

最佳答案

SELECT  u.name AS username,
        u.date_registered,
        COALESCE
                (
                (
                SELECT  COUNT(*) cnt
                FROM    video v
                JOIN    views vw
                ON      vw.video = v.video_id
                WHERE   v.uploaded_by = u.name
                GROUP BY
                        v.video_id
                ORDER BY
                        cnt DESC
                LIMIT 1
                ),
                0
                ) AS EmaxViews
FROM    users u

关于mysql - 连接具有不同行和多个选择语句的两个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13213948/