多表查询的sql语句

求一个多表查询的sql语句
A表10个字段
字段分别为aid, title, cid, content, atime, aorder, acount，a1, a2, mtime

B表9个字段
字段分别为bid, title, cid, content, btime, border, b3, b4, mtime

现在我获取到一个关键词$kw,我需要同时搜索A表和B表的title字段，获取到所有like %$kw%的所有记录,并根据mtime来排序。

获取的记录里，需要保留的字段有a(b)id,title,cid,content,mtime这几个。

SQL code

$sql = 'SELECT ';$sql .= 'A.aid as id,B.bid AS id,';$sql .= 'A.title as title,B.title as title,';$sql .= 'A.cid as cid,B.cid as cid,';$sql .= 'A.content as content,B.content as content,';$sql .= 'A.mtime as mtime,B.mtime as mtime';$sql .= 'FROM A,B WHERE title like "%'.$kw.'%" ORDER BY mtime DESC';

我这样构造sql,可行吗？

如果不可行，那么怎么写这个sql语句呢？

[解决办法]
去看一下 union 

两个 select 然后中间用一下 union , 要求 select 后面的字段都一样的

select a.id, a.xxx from a where a.title like '%$km%'
union
select b.id, b.xxx from b where b.title like '%$km%'
[解决办法]
select aid, title, cid, content,mtime from a where title like '%km%'
order by mtime desc

union all

select bid,title,cid,content,mtime from b where title like '%km%'
order by mtime desc

查看更多 下一篇