问题是这样的,我打算在一个表里获得与某一行记录相邻的两行,并且想通过union一起取出来,所以这么写:
select id,category,title,font,color,tag,url,`desc`,content,hits,iorder,passed,dateline from t1 where passed=1 and (title like '%三国%' or tag like '%三国%' or `desc` like '%三国%') order by dateline desc limit 0,20
union all
select id,category,title,font,color,tag,url,`desc`,content,hits,iorder,passed,dateline from t2 where passed=1 and (title like '%三国%' or tag like '%三国%' or `desc` like '%三国%') order by dateline desc limit 0,20
(必须保证搜索的两个表的列是相同的,否则会出现错误)
(union会把两个表中搜索出的结果经过计算去除相同的数据再返回的,而union all则不会,所以要比union快)
但出现了错误提示“Incorrect usage of UNION and ORDER BY”。看来不能这么用union和order by,但这里确实是需要order by的。很快,我想到了一个变通的写法:
select * from(select
id,category,title,font,color,tag,url,`desc`,content,hits,iorder,passed,dateline
from t1 where passed=1 and (title like '%三国%' or tag like '%三国%' or
`desc` like '%三国%') order by dateline desc limit 0,20 )
union all
select
id,category,title,font,color,tag,url,`desc`,content,hits,iorder,passed,dateline
from t2 where passed=1 and (title like '%三国%' or tag like '%三国%' or
`desc` like '%三国%') order by dateline desc limit 0,20
从经验上说,第二个子句该不会被union影响,可以用order
by。于是把第一个子句包在一个括号里,这下应该就可以了。可是还是有错误,提示“ Every derived table must have
its own alias”。这里的提示是需要给我们括号里面生成的临时表取一个别名,这个好办多了。于是改为:
select * from(select id,category,title,font,color,tag,url,`desc`,content,hits,iorder,passed,dateline from t1 where passed=1 and (title like '%三国%' or tag like '%三国%' or `desc` like '%三国%') order by dateline desc limit 0,20 )
union all as t3
select id,category,title,font,color,tag,url,`desc`,content,hits,iorder,passed,dateline from t2 where passed=1 and (title like '%三国%' or tag like '%三国%' or `desc` like '%三国%') order by dateline desc limit 0,20
这条语句成功执行并且得到了正确的结果。