柳暗花明笔记

    自写mysql 的SQL语句收藏case cast

    所属分类:[ linux&mysql ] 猫哲学家 发表于:2016-11-04 16:42 浏览:179

    SELECT
    ticket.ticket_id,
    ticket.`number`,
    ticket.staff_id,
    ticket.topic_id,
    ticket.lastmessage as 'date',
    cdata.subject,
    user.name as 'from',
    cdata.priority as pri,
    concat(staff.firstname,' ',staff.lastname) as assign_to,
    case 
    when ptopic.topic_pid>0 then concat(ptopic.topic,' / ', topic.topic)
    when ptopic.topic_pid<=0 then topic.topic
    end as topic,
    cast(greatest(ifnull(ticket.lastmessage, 0), ifnull(ticket.reopened, 0), ticket.created) as datetime) as effective_date
    from ost_ticket as ticket
    left join ost_ticket__cdata as cdata on ticket.ticket_id=cdata.ticket_id
    left join ost_user as user on ticket.user_id=user.id
    left join ost_staff as staff on ticket.staff_id=staff.staff_id
    left join ost_help_topic as topic on ticket.topic_id=topic.topic_id
    left join ost_help_topic as ptopic on topic.topic_pid=ptopic.topic_id
    left join ost_ticket_priority as pri on (cdata.priority_id=pri.priority_id)
    where ticket.status='open' and ticket.isanswered=0
    order by pri.priority_urgency asc,effective_date desc,ticket.ticket_id desc
    limit 5000

    标签: mysql 
    上一编文章 php如果在php.ini设置了display_errors = Off还可能会显示错误 下一编文章 大SQL语句收藏
    最近文章
    发表评论