查询SQL语句,会考虑优化的最好
查询SQL语句,能考虑优化的最好
表1:Orders 货单表
id int 主键
status char(1) 状态(0,1,2,3,4)
表2:Users 用户表
id int 主键
userName varchar(50) 用户名
表3:OrdersLog 日志表
id int 主键
orderId int 外键 对应Orders表主键
userId int 外键 对应Users表主键
status char(1) 同Orders表status
updateTime datetime
当Orders表中的状态发生改变时,会在OrdersLog中插入一条数据记录ordersId,userId 操作人,status 改变的状态,updatetime 更新时间.
我想查询出每一个货单最后更新的那条数据。一个货id对应一条数据
结果集包含的字段
orderId userName status updateTime
------解决方案--------------------
------解决方案--------------------
select orderId,userName, status, updateTime from
(select *,rn=row_number() over (partition by orderid order by updatetime desc) from orderslog) a,users
where a.userid=b.userid and rn='1'
------解决方案--------------------
try this, It 's better have an index on OrdersLog(orderId).
------解决方案--------------------
If he don't have index,what's the better?
表1:Orders 货单表
id int 主键
status char(1) 状态(0,1,2,3,4)
表2:Users 用户表
id int 主键
userName varchar(50) 用户名
表3:OrdersLog 日志表
id int 主键
orderId int 外键 对应Orders表主键
userId int 外键 对应Users表主键
status char(1) 同Orders表status
updateTime datetime
当Orders表中的状态发生改变时,会在OrdersLog中插入一条数据记录ordersId,userId 操作人,status 改变的状态,updatetime 更新时间.
我想查询出每一个货单最后更新的那条数据。一个货id对应一条数据
结果集包含的字段
orderId userName status updateTime
SQL查询
------解决方案--------------------
select
a.id as orderId,
b.userName,
c.status,
c.updateTime
from
Orders as a,Users as b,OrdersLog as c
where
a.id=c.orderId and b.id=c.userId
and
not exists(select 1 from OrdersLog where OrdersLog=c.OrdersLog and updateTime>c.updateTime)
------解决方案--------------------
select orderId,userName, status, updateTime from
(select *,rn=row_number() over (partition by orderid order by updatetime desc) from orderslog) a,users
where a.userid=b.userid and rn='1'
------解决方案--------------------
try this, It 's better have an index on OrdersLog(orderId).
select a.orderId,b.userName,a.status,a.updateTime
from OrdersLog a
inner join Users b on a.userId=b.id
inner join
(select orderId,max(id) 'maxid'
from OrdersLog
group by orderId) c on a.orderId=c.orderId and a.id=c.maxid
------解决方案--------------------
If he don't have index,what's the better?