SELECT * FROM (SELECT title, newstime, titleurl, classid, city, newspath,filename FROM ecms_info UNION SELECT title, newstime, titleurl, classid, city, newspath,filename FROM ecms_house) AS T ORDER BY newstime DESC LIMIT 10
以上为调出ecms_info和ecms_house表的最新消息的标题,所在地,标题链接,日期,栏目ID。希望把栏目ID转换成栏目名称,以及相应的栏目链接,这需要在另一个表enewsclass中查询classid,classname和classpath
select classid,classname,classpath from enewsclass用Left Join 把两个结果根据classid相同进行匹配查询,
SELECT a.title, newstime, titleurl, classid, city, newspath, filename, b.classid, classname, classpath
FROM (SELECT title, newstime, titleurl, classid, city, newspath, filename
FROM phome_ecms_info
UNION SELECT title, newstime, titleurl, classid, city, newspath, filename
FROM phome_ecms_house
) AS a
LEFT JOIN phome_enewsclass AS b ON a.classid = b.classid
ORDER BY newstime DESC
LIMIT 10 出现出错信息:#1052 - Column 'classid' in field list is ambiguous 请问高手,我的SQL应该怎么改?其他地方是否对?
FROM (SELECT title, newstime, titleurl, classid, city, newspath, filename
FROM phome_ecms_info
UNION SELECT title, newstime, titleurl, classid, city, newspath, filename
FROM phome_ecms_house
) AS a
LEFT JOIN phome_enewsclass AS b ON a.classid = b.classid
ORDER BY newstime DESC
LIMIT 10
其实错误提示已经很清楚了。这个classid在多个表中出现,mySQL无法断定你想指定的是哪一个。
似乎出错的不是第一段的classid,而是中间的union那段
表也有CLASSID,没有加别名不能确定是UNION的还是phome_enewsclass表
的CLASSID
先执行一下 SELECT title, newstime, titleurl, classid, city, newspath, filename
FROM phome_ecms_info
UNION SELECT title, newstime, titleurl, classid, city, newspath, filename
FROM phome_ecms_house 以确定这句没有问题。