SELECT *
FROM tb1 A
INNER JOIN tb2 B ON B.tbid = A.id
WHERE NOT EXISTS (
SELECT TOP 1 1
FROM tb2
WHERE scores > B.scores
AND tbid = B.tbid
)
FROM tb1 A
INNER JOIN tb2 B ON B.tbid = A.id
WHERE NOT EXISTS (
SELECT TOP 1 1
FROM tb2
WHERE scores > B.scores
AND tbid = B.tbid
)
group by tb1.id,tb1.fd1,tb1.fd2,tb2.time
from tb1 a inner join
(select tbid,scores=max(scores) from tb2 group by tbid)b
on a.id=b.tbid
inner join tb2 c
on b.bid=c.bid
from tb1 a,tb2 b
where a.id=b.tbid
group by a.id,a.fd1,a.fd2,b.time
select tb1.id,tb1.fd1,tb1.fd2,tb2.scores,tb2.time
from tb1 join tb2 on tb1.id=tb2.tbid
where tb2.scores=max(tb2.scores)
group by tb1.fd1
测试结果
1 80 2004-08-01 22:00:00.000 1 张三 test1
2 90 2004-08-01 22:00:00.000 2 李四 test2
聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。
(select tbid,sum(scores) as scores,min(time) as time from tb2 group by tbid)b
on a.id=b.tbid
Insert into tb1
select '1','張三','test1'
union all select '2','李四','test2'create table tb2(id int,tbid int,scores numeric(5),time datetime)
Insert into tb2
select '1','1','50','2004-08-01 22:00:00'
union all select '2','1','80','2004-08-01 22:00:00'
union all select '3','2','30','2004-08-01 22:00:00'
union all select '4','2','80','2004-08-01 22:00:00'
union all select '5','2','90','2004-08-01 22:00:00'select a.*,b.scores,b.time from tb1 a inner join
(select tbid,max(scores) as scores,max(time) as time from tb2 group by tbid)b
on a.id=b.tbid--結果
id fd1 fd2 scores time
----------------------------------------------------------------------
1 張三 test1 80 2004-08-01 22:00:00.000
2 李四 test2 90 2004-08-01 22:00:00.000
Insert into tb1
select '1','張三','test1'
union all select '2','李四','test2'
create table tb2(id int,tbid int,scores numeric(5),time datetime)
Insert into tb2
select '1','1','50','2004-08-01 22:00:00'
union all select '2','1','80','2004-08-01 22:00:00'
union all select '3','2','30','2004-08-01 22:00:00'
union all select '4','2','80','2004-08-01 22:00:00'
union all select '5','2','90','2004-08-01 22:00:00'select tb1.id,tb1.fd1,tb1.fd2,max(tb2.scores) as scores,tb2.time
from tb1,tb2
where tb1.id=tb2.tbid group by tb1.id,tb1.fd1,tb1.fd2,tb2.time
drop table tb1,tb2
2 李四 test2 90 2004-08-01 22:00:00.000
第一种:
SELECT *
FROM tb1 A
INNER JOIN tb2 B ON B.tbid = A.id
WHERE NOT EXISTS (
SELECT TOP 1 1
FROM tb2
WHERE scores > B.scores
AND tbid = B.tbid
)第二种:
select a.*,b.scores,b.time from tb1 a inner join
(select tbid,max(scores) as scores,max(time) as time from tb2 group by tbid)b
on a.id=b.tbid这两种方法都可做到,现在关键是哪种效率更高,第一种很通用,对tb2的字段不用一一用max处理,当tb2字段多时就很有用了,但就不知exists的效率如何,希望大家继续给些意见
left join
(select tbid ,max(scores) from tb2 group by tbid ) tb2 b on aid=b.tbid
where a.id=c.tbid
left join
(select tbid ,max(scores) from tb2 group by tbid ) tb2 b on aid=b.tbid
left join a.id=c.tbid
left join
(select tbid ,max(scores) from tb2 group by tbid ) b on aid=b.tbid
left join a.id=c.tbid