case when 嵌套 mysql_mysql case when嵌套使用

实例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