mysql - 为什么我没有得到我期望的值(value)？

Question

Here is a relevant SQLFiddle

查询:

SELECT 
        n.Type AS TYPE,
        n.UserIdn AS UserIdn,
        u.Username AS Username,
        n.NewsIdn AS NewsIdn,
        n.Header AS Header,
        n.Text AS TEXT,
        n.Tags AS Tags,
        n.ImageLink AS ImageLink,
        n.VideoLink AS VideoLink,
        n.DateCreate AS DateCreate,
        IFNULL(SUM(l.Type = 'up'),0) AS Uplikes, 
        IFNULL(SUM(l.Type = 'down'),0) AS Downlikes,
        (IFNULL(SUM(l.Type = 'up'),0) - IFNULL(SUM(l.Type = 'down'),0)) AS SumLikes,
        SUM(f.Type = 'up') AS CountFavorites
    FROM News n 
    INNER JOIN Users u ON n.UserIdn = u.UserIdn 
    LEFT JOIN Likes l ON n.NewsIdn = l.NewsIdn
    LEFT JOIN Favorites f ON n.NewsIdn = f.NewsIdn

    GROUP BY n.id 

我认为我的问题是值 CountFavorites - 对于 NewsIdn，我得到值 = 2 我期望 1。

图像结果(http://i.stack.imgur.com/NqD9u.jpg):

谁能帮我弄清楚为什么我的结果不符合预期？

Answer 1

SELECT 
            n.Type AS TYPE,
            n.UserIdn AS UserIdn,
            u.Username AS Username,
            n.NewsIdn AS NewsIdn,
            n.Header AS Header,
            n.Text AS TEXT,
            n.Tags AS Tags,
            n.ImageLink AS ImageLink,
            n.VideoLink AS VideoLink,
            n.DateCreate AS DateCreate,
            IFNULL(SUM(l.Type = 'up'),0) AS Uplikes, 
            IFNULL(SUM(l.Type = 'down'),0) AS Downlikes,
            (IFNULL(SUM(l.Type = 'up'),0) - IFNULL(SUM(l.Type = 'down'),0)) AS SumLikes,
            (select count(*) from favorites f where f.Type = 'up' and n.NewsIdn = f.NewsIdn) AS CountFavorites
        FROM News n 
        INNER JOIN Users u ON n.UserIdn = u.UserIdn 
        LEFT JOIN Likes l ON n.NewsIdn = l.NewsIdn
        -- LEFT JOIN Favorites f ON n.NewsIdn = f.NewsIdn

        GROUP BY       n.Type, n.UserIdn,u.Username,n.NewsIdn,Header,n.Text,n.Tags ,n.ImageLink, n.VideoLink , n.DateCreate

您的错误是加入一对多对多，重复行。如果您在多次连接后使用聚合，您将度过一段糟糕的时光。在此类情况下聚合 Before Join。

或者你可以把它写成

            select n.Type AS TYPE,
            n.UserIdn AS UserIdn,
            u.Username AS Username,
            n.NewsIdn AS NewsIdn,
            n.Header AS Header,
            n.Text AS TEXT,
            n.Tags AS Tags,
            n.ImageLink AS ImageLink,
            n.VideoLink AS VideoLink,
            n.DateCreate AS DateCreate,
              Uplikes, 
             Downlikes,
            SumLikes,
 CountFavorites
inner join users on ...
left join (
select l.news_idn, 
IFNULL(SUM(l.Type = 'down'),0) AS Downlikes,
(IFNULL(SUM(l.Type = 'up'),0) - IFNULL(SUM(l.Type = 'down'),0)) AS SumLikes
from likes) l
on n.NewsIdn = l.NewsIdn

left join (select newsidn ,count(*) CountFavorites from favorites f where f.Type = 'up') f on i.newsidn=n.newsidn