mysql 两个时间比较其中一个可能为null 解决办法

最新推荐文章于 2024-07-18 04:06:27 发布

大道至简_lyon

最新推荐文章于 2024-07-18 04:06:27 发布

阅读量5.5k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/a5601564/article/details/78208058

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

mysql 两个时间比较其中一个可能为null

SELECT * FROM (
SELECT qi.id,qci.id comment_id,qi.question_time,
if(qi.question_time>IFNULL(qci.comment_time,0),qi.question_time,qci.comment_time) newest_time
from question_info qi
LEFT JOIN question_comment_info qci on qci.question_id = qi.id
/* 用户头像 */
LEFT JOIN user_info u ON u.id = qi.user_id AND u.disable_flag = 0
LEFT JOIN user_info uu on uu.id = qci.user_id AND uu.disable_flag = 0
/* 语音信息 */
LEFT JOIN record_info rci on rci.comment_id = qci.id
/* 订单信息，判断偷听 */
LEFT JOIN user_balance_payment ubp on ubp.payment_program_id = qci.id and ubp.type_id=6 and ubp.user_id = 3491
ORDER BY newest_time desc
) a

第一重null为0

第二重比较排序

第三重比较排序

经过多重比较

终于成功地把 mysql 两个时间比较其中一个可能为null 排序成功解决

mysql 复杂多个字段时间排序，时间字段有null

SELECT * from (
SELECT qi.id question_id,qi.question_content,qi.question_desc,qi.type_id ,qi.comment_num
        question_comment_num,
        u.user_name ,qi.user_id,qi.question_time,
        qi.question_view_num,qi.invited_person_id,qi.is_anonymous, u.pic_name user_pic,u.head_img_url user_headimgurl,
        qci.id commentedId,qci.comment_content,qci.user_id comment_user_id,qci.comment_num,qci.good_num
        ,commentu.user_name commenter_name,commentu.head_img_url commenter_headimgurl,commentu.pic_name
        commenter_picname ,
        rci.record_name,rci.duration,
				if(TIMESTAMPDIFF( MINUTE, qi.question_time, now() )>3*24*60,1,0) time_out
        ,case when qgi_one.read_flag >=0 then 1 else 0 end good_flag
        ,ubp.id ubp_id,if(TIMESTAMPDIFF( MINUTE,qci.comment_time, now())>60*3,1,0) comment_time_out,
if(qi.question_time>IFNULL(qci.comment_time,0),qi.question_time,qci.comment_time) newest_time
from question_info qi
LEFT JOIN question_comment_info qci on qci.question_id = qi.id
/* 用户头像 */
LEFT JOIN user_info u ON u.id = qi.user_id  AND u.disable_flag = 0 
LEFT JOIN user_info commentu on commentu.id = qci.user_id AND commentu.disable_flag = 0 
/* 语音信息 */
LEFT JOIN record_info rci on rci.comment_id = qci.id 
/* 订单信息，判断偷听 */
LEFT JOIN user_balance_payment ubp on ubp.payment_program_id = qci.id and ubp.type_id=6  and ubp.status_id =2 and ubp.user_id = 3491 
/* 点赞系统，判断是否已经点赞成功 */
LEFT JOIN question_goods_info qgi_one on qgi_one.comment_id = qci.id and qgi_one.user_id = 3491
WHERE qi.id not in (
	select qi.id question_id
	from question_info qi 
	LEFT JOIN question_comment_info qci on qi.id = qci.question_id 
	LEFT JOIN record_info rci on rci.comment_id =qci.id 
	LEFT JOIN question_goods_info qgi_one on qgi_one.comment_id = qci.id and qgi_one.user_id = 3491
	LEFT JOIN user_balance_payment ubp on ubp.payment_program_id = qi.id and ubp.type_id=6  and ubp.status_id =2 and ubp.user_id = 3491 
	where qi.disable_flag = 0 AND is_anonymous = 1 and invited_person_id is not NULL and record_name is NULL   and (qi.user_id= 3488 or qci.user_id= 3488)
)
and is_anonymous = 1 and qi.disable_flag = 0   and (qi.user_id= 3488 or qci.user_id= 3488)
 group by newest_time 
UNION
SELECT  qi.id question_id,qi.question_content,qi.question_desc,qi.type_id ,qi.comment_num
        question_comment_num,
        u.user_name ,qi.user_id,qi.question_time newest_time,
        qi.question_view_num,IFNULL(qi.invited_person_id,100),qi.is_anonymous, u.pic_name user_pic,u.head_img_url user_headimgurl,
        qci.id commentedId,qci.comment_content,qci.user_id comment_user_id,qci.comment_num,qci.good_num
        ,commentu.user_name commenter_name,commentu.head_img_url commenter_headimgurl,commentu.pic_name
        commenter_picname ,
        rci.record_name,rci.duration,
				if(TIMESTAMPDIFF( MINUTE, qi.question_time, now() )>3*24*60,1,0) time_out
        ,case when qgi_one.read_flag >=0 then 1 else 0 end good_flag
        ,ubp.id ubp_id,if(TIMESTAMPDIFF( MINUTE,qci.comment_time, now())>60*3,1,0) comment_time_out,
				qi.question_time newest_time
from question_info qi
LEFT JOIN question_comment_info qci on qci.question_id = qi.id
/* 用户头像 */
LEFT JOIN user_info u ON u.id = qi.user_id  AND u.disable_flag = 0 
LEFT JOIN user_info commentu on commentu.id = qci.user_id AND commentu.disable_flag = 0 
/* 语音信息 */
LEFT JOIN record_info rci on rci.comment_id = qci.id 
/* 订单信息，判断偷听 */
LEFT JOIN user_balance_payment ubp on ubp.payment_program_id = qci.id and ubp.type_id=6  and ubp.status_id =2 and ubp.user_id = 3491 
/* 点赞系统，判断是否已经点赞成功 */
LEFT JOIN question_goods_info qgi_one on qgi_one.comment_id = qci.id and qgi_one.user_id = 3491
WHERE qi.id not in (
	select qi.id question_id
	from question_info qi 
	LEFT JOIN question_comment_info qci on qi.id = qci.question_id 
	LEFT JOIN record_info rci on rci.comment_id =qci.id 
	LEFT JOIN question_goods_info qgi_one on qgi_one.comment_id = qci.id and qgi_one.user_id = 3491
	LEFT JOIN user_balance_payment ubp on ubp.payment_program_id = qi.id and ubp.type_id=6  and ubp.status_id =2 and ubp.user_id = 3491 
	where qi.disable_flag = 0 AND is_anonymous = 1 and invited_person_id is not NULL and record_name is NULL   and (qi.user_id= 3488 or qci.user_id= 3488)
)
and is_anonymous = 1 and qi.disable_flag = 0   and qi.user_id=qci.user_id and qi.user_id=3488
 group by newest_time 
) b 
ORDER BY newest_time desc