意图:根据表中的price字段的值,赋值提问或回答,以下表和字段都是存在的。
SELECT tid, subject, price,
CASE price
WHEN price <0
THEN '提问'
WHEN price >0
THEN '回答'
END AS 状态
FROM discuz.cdb_threads
WHERE fid =4
ORDER BY lastpost DESC
LIMIT 8 如果price>0状态为回答,<0则为提问,测试好多次了还是不行,急死了!
SELECT tid, subject, price,
CASE price
WHEN price <0
THEN '提问'
WHEN price >0
THEN '回答'
END AS 状态
FROM discuz.cdb_threads
WHERE fid =4
ORDER BY lastpost DESC
LIMIT 8 如果price>0状态为回答,<0则为提问,测试好多次了还是不行,急死了!
from discuz.cdb_threads
where fid =4
order by lastpost desc
limit 8 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
CASE
WHEN price <0
THEN '提问'
WHEN price >0
THEN '回答'
END AS 状态
FROM discuz.cdb_threads
WHERE fid =4
ORDER BY lastpost DESC
LIMIT 8
CASE price
WHEN if(price=null,0,price) <0
THEN '提问'
WHEN if(price=null,0,price)>0
THEN '回答'
END AS 状态
FROM discuz.cdb_threads
WHERE fid =4
ORDER BY lastpost DESC
LIMIT 8
WHEN price <0 --------->关键是上面红色部分
如果你的字段名出现在when里面判断的话,则case后不用再跟对应字段名了;若when里面不用该字段名进行判断,则把字段名写在case后就ok了,如:方式一:
case 字段名 when 0 then 'a' when 1 then 'b' else 'c' end;
方式二:
case when 字段名=0 then 'a' when 字段名=1 then 'b' else 'c' end;