我有一个查询，它有一个交叉连接和一个产生错误的左连接
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;
