SELECT a.Sno,ISNULL(b.Score,0) AS Score
FROM tbA AS a
LEFT JOIN
(
SELECT Sno,MAX(Score) AS Score
FROM tbB
GROUP BY Sno
) AS b
ON a.Sno=b.Sno;
FROM tbA AS a
LEFT JOIN
(
SELECT Sno,MAX(Score) AS Score
FROM tbB
GROUP BY Sno
) AS b
ON a.Sno=b.Sno;
调试欢乐多
select sno,max(score) as score
from a left join b on a.sno=b.sno
group by sno
FROM tbA AS a
LEFT JOIN tbA b ON a.Sno=b.Sno
GROUP BY a.Sno
--> 生成测试数据: @tbA
DECLARE @tbA TABLE (Sno VARCHAR(2),Sname VARCHAR(6))
INSERT INTO @tbA
SELECT '01','李连杰'
--> 生成测试数据: @tbB
DECLARE @tbB TABLE (Sno VARCHAR(2),Score INT)
INSERT INTO @tbB
SELECT '01',60 UNION ALL
SELECT '01',70 UNION ALL
SELECT '01',90--SQL查询如下:SELECT a.Sno,a.Sname,ISNULL(b.Score,0) AS Score
FROM @tbA AS a
LEFT JOIN
(
SELECT Sno,MAX(Score) AS Score
FROM @tbB
GROUP BY Sno
) AS b
ON a.Sno=b.Sno;/*
Sno Sname Score
---- ------ -----------
01 李连杰 90(1 行受影响)
*/
INSERT INTO #tbA
SELECT '01','李连杰'
create TABLE #tbB(Sno VARCHAR(2),Score INT)
INSERT INTO #tbB
SELECT '01',60 UNION ALL
SELECT '01',70 UNION ALL
SELECT '01',90
SELECT a.Sno,a.Sname,max(b.Score) AS Score
FROM #tbA AS a
LEFT JOIN #tbB b ON a.Sno=b.Sno
GROUP BY a.Sno,a.Snamedrop table #tbA
drop table #tbB
结果:
01 李连杰 90
Select a.Sno,a.Sname,b.Score
From 表A As a
Left Outer Join 表B As b On b.Sno=a.Sno And b.Score=(Select max(Score) From 表B Where Sno=b.Sno)
create table b(Sno int,Sname varchar(10))create table c(Sno int,Score int)insert into b values(01,'李连杰')
insert into c values(01,60)
insert into c values(01,70)
insert into c values(01,90)select b.Sno,Sname,max(Score) as Score from b as b left join c as c on b.Sno=c.Sno group by b.Sno,Sname
from A,(select Sno,max(score) from B group by B.Sno)B
where A.sno=B.sno