sql server的代码如下,我需要把语句改成mysql,实现每个人在每个交易日期是第几单交易,谢谢!
go
drop table #r
go
select distinct CustomerID,Transactiondate,transactioncode
into #r
from [Trans_Master]
order by CustomerID,Transactiondate,transactioncodego
alter table #r add Rep float
go
select CustomerID,Transactiondate,row_number()over(partition by CustomerID order by Transactiondate)Rep
from #r order by CustomerID;
with CTE as(
select CustomerID,Transactiondate,Rep,row_number()over(partition by CustomerID order by Transactiondate)RN
from #r
)
update CTE
set Rep=RNgo
alter table [Trans_Master] add Rep float
go
update [Trans_Master]
set Rep=b.Rep
from [Trans_Master] a,#r b
where a.CustomerID=b.CustomerID
and a.Transactiondate=b.Transactiondate
and a.transactioncode=b.transactioncode
go
drop table #r
go
select distinct CustomerID,Transactiondate,transactioncode
into #r
from [Trans_Master]
order by CustomerID,Transactiondate,transactioncodego
alter table #r add Rep float
go
select CustomerID,Transactiondate,row_number()over(partition by CustomerID order by Transactiondate)Rep
from #r order by CustomerID;
with CTE as(
select CustomerID,Transactiondate,Rep,row_number()over(partition by CustomerID order by Transactiondate)RN
from #r
)
update CTE
set Rep=RNgo
alter table [Trans_Master] add Rep float
go
update [Trans_Master]
set Rep=b.Rep
from [Trans_Master] a,#r b
where a.CustomerID=b.CustomerID
and a.Transactiondate=b.Transactiondate
and a.transactioncode=b.transactioncode
CREATE TEMPORARY TABLE IF EXISTS `#r` AS
SELECT DATA.*,
@id := if(@p=CustomerID, @id+1, 1) as Rep ,
@p := CustomerID
FROM(
select distinct CustomerID,Transactiondate,transactioncode
from Trans_Master
order by CustomerID,Transactiondate,transactioncode
) DATA,
( SELECT @id := NULL, @p := NULL ) ID
ORDER BY DATA.CustomerID, DATA.Transactiondate
;
SELECT * FROM `#r`;alter table [Trans_Master] add Rep float;
update Trans_Master a,`#r` b
set a.Rep=b.Rep
where a.CustomerID=b.CustomerID
and a.Transactiondate=b.Transactiondate
and a.transactioncode=b.transactioncode;