实例1
SELECT id, type, content, uid2, time, readflag, CASE WHEN uid2 > 0 THEN readflag WHEN uid2 = 0
THEN CASE WHEN ( SELECT COUNT(*) FROM `tbl_SytemMsgRead` WHERE tbl_SytemMsgRead.`uid` = 5193 AND tbl_SytemMsgRead.`msgid`
= database.tbl_SytemMsg.id ) > 0 THEN 1 ELSE 0 END END sortid FROM database.tbl_SytemMsg WHERE time > -62170013152
AND id NOT IN(select msgid FROM database.tbl_SytemMsgRead WHERE uid = 5193 AND flag = 1) AND uid2 in(0,5193)
ORDER BY sortid ASC , id DESC LIMIT 0,10
实例2
SELECT AcProject.ac_id,AcProject.ac_title,AcProject.ac_multi_flag,CASE WHEN AcProject.ac_multi_flag = 1 THEN CASE
WHEN ( SELECT ProjectTime.`ac_time` FROM `tbl_AcProjectTime` ProjectTime WHERE ProjectTime.`ac_project_id` = AcProject.`ac_id`
AND ac_time > UNIX_TIMESTAMP() ORDER BY ac_time ASC LIMIT 1) != '' THEN ( SELECT ProjectTime.`ac_time` FROM
`tbl_AcProjectTime` ProjectTime WHERE ProjectTime.`ac_project_id` = AcProject.`ac_id` AND ac_time > UNIX_TIMESTAMP()
ORDER BY ac_time ASC LIMIT 1) ELSE AcProject.ac_time END ELSE AcProject.ac_time END showtime ,(SELECT COUNT(*)
FROM database.`tbl_AcSingUpProject` AcSingUpProject WHERE AcSingUpProject.ac_project_id = AcProject.`ac_id` AND
AcSingUpProject.`ac_flag` = 0 ) AS COUNT, ( SELECT `ac_pic_url` FROM database.tbl_AcProjectPic tbl_AcProjectPic WHERE
AcProject.`ac_id` = tbl_AcProjectPic.`ac_prject_id` ORDER BY tbl_AcProjectPic.ac_id LIMIT 1) ac_pic_url FROM
database.tbl_AcProject AcProject WHERE AcProject.ac_flag IN (1,2,5) AND AcProject.ac_type = '0' ORDER BY TIME DESC LIMIT 0,5
mysql case when嵌套使用
最新推荐文章于 2024-06-22 06:30:00 发布