有这么难理解吗?就是当:SELECT * FROM ads WHERE sid='11' AND cid='16' ORDER BY RAND() LIMIT 1 上面这条语句没查到数据时,让它转为:SELECT * FROM ads WHERE sid='11' AND cid='0' ORDER BY RAND() LIMIT 1 我现在是分开写,想把这二句整在一起,写成一句SQL,二楼的大哥的查询结果是正确的,我只是觉得有个子查询不大好,
猜一下了 SELECT * FROM ads WHERE sid='11' AND (cid='16' or cid='0') ORDER BY cid desc ,RAND() LIMIT 1
不是这样子的,这样的话当cid为16及为0都出来了,我再描述一下吧,ads表里的cid默认为0,我想让它查询时cid=16 的优先,当该条件没有时才显示cid=0的数据.SELECT * FROM ads WHERE cid='16' ORDER BY RAND() LIMIT 1
楼主,假设有如下记录 sid cid 11 0 8 16 2楼的方法会查出你要的结果吗?
查得出,当sid=11 and cid=16没有时,它就用上了sid=11 and cid=0,就符合你的那个示例记录了.
I bet it is:(SELECT * FROM ads WHERE sid='11' AND cid='16' ORDER BY RAND() limit 1)union(SELECT * FROM ads WHERE sid='11' AND cid='0' ORDER BY RAND() limit 1)
好象少了一截: (SELECT * FROM ads WHERE sid='11' AND cid='16' ORDER BY RAND() limit 1)union(SELECT * FROM ads WHERE sid='11' AND cid='0' ORDER BY RAND() limit 1)limit 1 哈哈哈哈
SELECT * FROM ads WHERE sid='11'
AND
cid=CASE WHEN EXISTS(SELECT 1 FROM ADS WHERE cid='16') THEN '16' ELSE '0' END
ORDER BY
RAND()
LIMIT 1
http://www.accessbbs.cn/bbs/viewthread.php?tid=19546
上面这条语句没查到数据时,让它转为:SELECT * FROM ads WHERE sid='11' AND cid='0' ORDER BY RAND() LIMIT 1
我现在是分开写,想把这二句整在一起,写成一句SQL,二楼的大哥的查询结果是正确的,我只是觉得有个子查询不大好,
数据如下
ads 1 AA C001
2 BB C002基于上面数据我期望结果为1 AA-C001
SELECT * FROM ads WHERE sid='11' AND (cid='16' or cid='0') ORDER BY cid desc ,RAND() LIMIT 1
sid cid
11 0
8 16
2楼的方法会查出你要的结果吗?
查得出,当sid=11 and cid=16没有时,它就用上了sid=11 and cid=0,就符合你的那个示例记录了.
(SELECT * FROM ads WHERE sid='11' AND cid='16' ORDER BY RAND() limit 1)union(SELECT * FROM ads WHERE sid='11' AND cid='0' ORDER BY RAND() limit 1)limit 1
哈哈哈哈