有这样的两张表
表1 有 cardid name
1 张
2 王
表2有 cardid start(时间) 1 '2010-03-12 12:00:00'
1 '2010-03-22 14:30:00'
2 '2010-02-16 16:32:20'
2 '2010-03-15 18:32:12'
2 '2010-03-22 13:12:12'
我想把这两张表连接起来 显示张,王两人 分别取Start时间最大的数据显示
由于表2里数据量比较大有没有效率高的查询语句?
表1 有 cardid name
1 张
2 王
表2有 cardid start(时间) 1 '2010-03-12 12:00:00'
1 '2010-03-22 14:30:00'
2 '2010-02-16 16:32:20'
2 '2010-03-15 18:32:12'
2 '2010-03-22 13:12:12'
我想把这两张表连接起来 显示张,王两人 分别取Start时间最大的数据显示
由于表2里数据量比较大有没有效率高的查询语句?
--方法1
select a.*,b.name from 表2 a,表1 b where a.cardid=b.cardid and start in (select max(start) from 表2 where cardid=a.cardid)--方法2
select a.*,b.name from 表2 a,表1 b where a.cardid=b.cardid and not exists
(select 1 from 表2 where cardid=a.cardid and start>a.start)
a.*,b.start
from
表1 a,表2 b
where
a.cardid=b.cardid
and
b.start=(select max(b.start) from 表2 where cardid=a.cardid)
--try
select a.*
from tablename2 a
left join tablename1 b on a.name = b.name
where not exists(select 1 from tablename2 where a.name= name and star>a.star)
http://topic.csdn.net/u/20100323/19/3b07ef2a-64ca-48af-b7a0-b9e35ff49d57.html?seed=1981854465&r=64134224#r_64134224
;WITH CTE1 AS(
SELECT CardID,Start,RankValue=RANK() OVER(PARTITION BY CardID,ORDER BY Start)
FROM table2
)
SELECT b.CardID,b.Name,a.Start FROM CTE1 AS a INNER JOIN table1 AS b ON b.CardID=a.CardID
WHERE a.RankValue=1