MySQL函数

最新推荐文章于 2024-04-20 15:52:58 发布

Ginoy

最新推荐文章于 2024-04-20 15:52:58 发布

阅读量176

点赞数

分类专栏： [老达笔记]Database

本文链接：https://blog.csdn.net/yl3395017/article/details/78466059

版权

[老达笔记]Database 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

oracle函数转换mysql

（1）row number over partition by

SELECT @rownum:=@rownum+1 rownum, NAME_ FROM  
(SELECT @rownum:=0,act_ge_property.* FROM act_ge_property GROUP BY rev_ ORDER BY NAME_ LIMIT 10) t

SELECT * FROM act_ge_property;


SELECT
tt.event_id,
ROW_NUMBER() OVER(PARTITION BY tt.aggr_id ORDER BY tt.event_id) AS num_id
FROM
t_aggr_raw_map tt,t_event tx
WHERE tt.event_id = tx.message_id
GROUP BY tt.aggr_id
ORDER BY ORDER BY tt.event_id
		           
		           
SELECT name_,value_,rev_ ,rank FROM (  
SELECT b.name_,b.value_,b.rev_,@rownum:=@rownum+1 ,  
IF(@rev_=b.rev_,@rank:=@rank+1,@rank:=1) AS rank, @rev_:=b.rev_  
FROM 
(SELECT name_,value_,rev_ FROM act_ge_property ORDER BY rev_ ) b,
(SELECT @rownum :=0 ,@rank:=0) c 
)  d;


SELECT 'X' FROM (
SELECT event_id,num FROM (  
SELECT b.aggr_id,b.event_id,@rownum:=@rownum+1 ,  
IF(@aggr_id=b.aggr_id,@num:=@num+1,@num:=1) AS num, @aggr_id:=b.aggr_id  
FROM 
(SELECT aggr_id,event_id FROM 
(SELECT  tt.aggr_id AS aggr_id,tt.event_id AS event_id
   FROM t_aggr_raw_map tt, t_event tx
  WHERE tt.event_id = tx.message_id )
  e ORDER BY e.event_id ) b,
(SELECT @rownum :=0 ,@num:=0) c 
)  d) F;


DELETE FROM t_event;
DELETE FROM t_aggr_raw_map;
SELECT LAST_INSERT_ID()