Sqlserver和Mysql 按照時間排序給單據編序號 獲取tag標記的顏色順序

按照時間排序給單據編序號

seq=1 就是最後一次tag標記的顏色

Sqlserver

SELECT row_number() over(PARTITION BY a.mail_guid ORDER BY b.ie_ymd+b.ie_time DESC)AS tag_seq,
a.mail_no,b.tag_id,d.tag_name,d.tag_color 
 FROM lrtdemail a
 JOIN lrmsgtag b ON a.mail_guid=b.msg_guid AND b.user_no='D025575' AND b.tag_type='T'
 JOIN lrtags d ON b.tag_id=d.tag_id 

 Mysql

 SELECT a.seq,a.mail_no,a.tag_id,a.tag_name,a.tag_color FROM (  
	SELECT a.mail_no,a.tag_id,a.tag_name,a.tag_color,@rownum:=@rownum+1 ,  
		IF(@pdept=a.mail_no,@rank:=@rank+1,@rank:=1) AS seq,  
		@pdept:=a.mail_no  
		FROM (   
			SELECT a.mail_no,b.tag_id,d.tag_name,d.tag_color 
			FROM lrtdemail a
			JOIN lrmsgtag b ON a.mail_guid=b.msg_guid AND b.user_no='D025575' AND b.tag_type='T'
			JOIN lrtags d ON b.tag_id=d.tag_id 
			ORDER BY a.mail_no DESC,CONCAT(b.ie_ymd,b.ie_time) DESC			
			) a 
			,
			(SELECT @rownum :=0 , @pdept := NULL ,@rank:=0
			) b
) a   
ORDER BY a.mail_no,a.seq ASC