SELECT * , 1 AS xh
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
AND `title` LIKE '%丹麦%'
)
UNION SELECT * , 2 AS xh
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
OR `title` LIKE '%丹麦%'
)
ORDER BY xh
LIMIT 6
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
AND `title` LIKE '%丹麦%'
)
UNION SELECT * , 2 AS xh
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
OR `title` LIKE '%丹麦%'
)
ORDER BY xh
LIMIT 6
2 AS xh
因此总体来说其实是没有重复的。
没法试, 有错你调一下就行,
不知道你原始需求, 有可能可以找到更简洁的写法, select ...., group_concat(xh) as cxh
from
(
SELECT * , 1 AS xh
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
AND `title` LIKE '%丹麦%'
)
UNION SELECT * , 2 AS xh
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
OR `title` LIKE '%丹麦%'
)
) temp
group by ....
where cxh in (1,2)
ORDER BY cxh
LIMIT 6
搜"德国 丹麦"的时候, 他要把两个词都包括的放前面, 只含一个的放后面
不过这样的话, 他那个
`title` LIKE '%德国%' OR `title` LIKE '%丹麦%'
应该加个括号
(`title` LIKE '%德国%' OR `title` LIKE '%丹麦%')
要不结果会有问题
id <312 AND `title` LIKE '%德国%' OR `title` LIKE '%丹麦%'
相当于 ( id <312 AND `title` LIKE '%德国%' ) OR `title` LIKE '%丹麦%' 这个和
id <312 AND ( `title` LIKE '%德国%' OR `title` LIKE '%丹麦%' )
是不同的, 应该明白了吧, 还不明白加条 有"丹麦"并且 id>312的记录 并且能让它排进前6的话就明白了
from
(SELECT * , 1 AS xh
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
AND `title` LIKE '%丹麦%'
)
UNION SELECT * , 2 AS xh
FROM text
WHERE (
id <312
AND (`title` LIKE '%德国%'
OR `title` LIKE '%丹麦%')
)
)temp
ORDER BY temp.xh
LIMIT 6
FROM text
WHERE (
id <312
AND `title` LIKE '%德国%'
AND `title` LIKE '%丹麦%'
)
UNION SELECT * , 2 AS xh
FROM text
WHERE (
id <312
AND (`title` LIKE '%德国%')+(`title` LIKE '%丹麦%') = 1
ORDER BY xh
LIMIT 6
学习了... 原来症结就在于楼主用or得到的结果和用and的结果集有重合, 改成 (`title` LIKE '%德国%')+(`title` LIKE '%丹麦%') = 1就不会重复了.
不过别人(或几个月后的你自己)看到这个sql可能会一下子反应不过来.所以要加注释另外,注意这个方法依赖于字段的collation,一定要是大小写无关的.