select u.id userid,u.realname,cs.snname,cs.sequencenumber
from user_info u
left join ( select * from check_info) c on c.userid=u.id
left join checksn cs on c.checksnid=cs.id
where u.schoolid=7 and u.schoolyear=2010 and u.schoolclass=1
order by sequencenumber desc
以上语句 三表联合查询结果如下
但我想根据userid筛选一次，条件是取userid 拥有的sequencenumber值最大的那条
先谢了。
------解决方案--------------------
try this,
select userid,realname,snname,sequencenumber
from
(select u.id userid,
u.realname,
cs.snname,
cs.sequencenumber,
row_number() over(partition by u.id order by cs.sequencenumber desc) 'rn'
from user_info u
left join check_info c on c.userid=u.id
left join checksn cs on c.checksnid=cs.id
where u.schoolid=7 and u.schoolyear=2010 and u.schoolclass=1) t
where rn=1
order by sequencenumber desc
------解决方案--------------------
with ta as
(select u.id userid,u.realname,cs.snname,cs.sequencenumber
from user_info u
left join ( select * from check_info) c on c.userid=u.id
left join checksn cs on c.checksnid=cs.id
where u.schoolid=7 and u.schoolyear=2010 and u.schoolclass=1
)
select top 1 * from ta order by sequencenumber desc