查询中的交叉连接和左连接

我有一个查询，它有一个交叉连接和一个产生错误的左连接

 Unknown column 'a.hour' in 'on clause'

查询的简化版是这样的

SELECT b.*,a.hour,c.datetime
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

但是当这个查询写如下时它可以工作。

select f.*,c.datetime
from
(select a.Hour,b.sec FROM a, b)f
LEFT JOIN c ON hour(c.datetime) = f.hour;

即使这个查询有效，但结果不是我所期望的

select b.*,a.hour.c.datetime
from
a
LEFT JOIN c ON hour(c.datetime) = hrs.hour,b;

这对我来说看起来很奇怪。谁能向我解释为什么会这样？我在 mysql 5.0 和 5.6 上试过这个。两者都有相同的结果。

贝娄是我正在处理的实际查询。如果它看起来太大和笨拙是因为我试图实现完全外连接而不重复两次主要查询之一。（左右连接）。Hrs表包含 1 到 24 和文本，如“1:00 到 2:00 Pm”。

SELECT DnT.text,IFNULL(BrowsingTime.username,upAppUsage.username) as  username,dDate,
IFNULL(BrowsingTime.wDay,upAppUsage.wDay) as wDay,
productive,
unProductive,
Total,
Game,
Chat,
Video
FROM
(
    SELECT hrs.hour,hrs.text,dates.dDate
    FROM
    hrs,
    -- ALL THE user,DATE WITH REPORT
    (
        SELECT date(surfDateTime) as dDate,userid
        FROM browsingtime
        WHERE
        DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
        UNION
        SELECT date(snapshotDateTime)as dDate,userid
        FROM AppUsage_user_hour
        WHERE
        DATE(snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
    )dates
) AS DnT
LEFT JOIN
-- USER BROWSING TIME PER HOUR PER DATE
(
    SELECT rb.userId,CONCAT('(',user.id,') ',user.username) AS username, 
    date(surfDateTime) surfdate, HOUR(surfDateTime)as hr,DAYNAME(surfDateTime) AS wDay,
    SUM(IF(up.isProductive = 1,rb.duration,0)) as productive , 
    SUM(IF(up.isProductive = 0,rb.duration,0)) as unProductive,
    SUM(rb.duration) as Total
    FROM `user` 
    INNER JOIN usergroup ON usergroup.id = `user`.userGroupId 
    INNER JOIN browsingtime rb ON `user`.id =  rb.userId
    LEFT OUTER JOIN url_productive up ON up.urlshortid = rb.urlShortId 
    WHERE
    DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
    AND user.userGroupId IN (1,2,3,4,5,6,7,8,9,10) 
    GROUP BY surfdate, hr,rb.userId
)as BrowsingTime ON DnT.hour = BrowsingTime.hr AND BrowsingTime.surfdate = DnT.dDate AND DnT.userId = BrowsingTime.userId
LEFT JOIN
-- APPLICATION USAGE
(
    SELECT r.userId,CONCAT('(',u.id,') ',u.username) AS username,
    date(r.snapshotDateTime)as usageDate, HOUR(r.snapshotDateTime)as hr,DAYNAME(r.snapshotDateTime) AS wDay,
    SUM(IF(sl.categoryId = 2 , softwareActiveTime,0)) as Game ,
    SUM(IF(sl.categoryId = 9 , softwareActiveTime,0)) as chat ,
    SUM(IF(sl.categoryId = 8 , softwareActiveTime,0)) as video 
    FROM AppUsage_user_hour r 
    INNER JOIN USER u ON u.id = r.userId
    INNER JOIN usergroup ON usergroup.id = u.userGroupId 
    INNER JOIN clientpc cp ON cp.id = r.pcId  
    INNER JOIN softwares sl ON sl.id = r.softwareId 
    WHERE sl.categoryId IN (2,8,9)
    AND  DATE(r.snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10' 
    GROUP BY usageDate,r.userId
)as upAppUsage ON DnT.hour = upAppUsage.hr AND upAppUsage.usageDate = DnT.dDate AND DnT.userId = upAppUsage.userid
WHERE
IFNULL(BrowsingTime.userId,upAppUsage.userId) IS NOT NULL;