高分请教SQL语句

[仙侣步惊云]

表M:nsid,xm,school
表A:nsid,ksdc
表B:nsid,kscs,ksdc
ksdc为考试等次,kscs为考试次数
查询:
M.nsid,M.xm,M.school,A.ksdc,B.kscs,B.Ksdc
一个nsid在A中只有一条记录,一个nsid在B中有多条记录,查询结果中求kscs最大的记录
表B的一些记录:
nsid kscs ksdc
1002001 1 不合格
1002001 2 不合格
1002001 3 不合格---查此记录
1002009 1 不合格
1002009 2 合格 --查此记录
1002011 1 不合格 --查此记录

[iqlife]

SELECT nsid,MAX(kscs)
FROM B
GROUP BY NSID

[Phoenix_99]

create table department(
id number(10) primary key not null,businessid number(10),datetime date,time varchar2(300),contents varchar2(10000))
create sequence dep_seq increment by 1 start with 1

[仙侣步惊云]

SELECT nsid,MAX(kscs)
FROM B
GROUP BY NSID
没有查询到 b.ksdc

[仙侣步惊云]

自己写了一下:
select M.NSID, m.xm, m.xb, M.SCHOOL,a.ksdc,b.kscs,b.ksdc
from tbjbxx M
left join tbksone a on a.nsid=m.nsid
left join
(select first nsid,kscs,ksdc
from tbzk b
-- order by kscs //此次一排序就通不过编译,出错
)b on m.nsid = b.nsid

[iqlife]

select * from B
where (nsid,kscs) in
(SELECT nsid,MAX(kscs)
FROM B
GROUP BY NSID)

[suiziguo]

select M.nsid,M.xm,M.school,A.ksdc,B.kscs,B.Ksdc
from M,A,B,
(select nsid,max(kscs) from B group by nsid) C
where M.nsid=A.nsid and A.nsid=B.nsid and B.nsid=C.nsid;

[suiziguo]

select M.nsid,M.xm,M.school,A.ksdc,B.kscs,B.Ksdc
from M,A,B,
(select nsid,max(kscs) kscs from B group by nsid) C
where M.nsid=A.nsid and A.nsid=B.nsid and B.nsid=C.nsid and B.kscs=C.kscs;


少了一个条件

[dawugui]

select m.* , a.ksdc,t.kscs,t.Ksdc 
from m , a , b t
where m.nsid = a.nsid and m.nsid = t.nsid and t.kscs = (select max(kscs) from b where nsid = t.nsid)

select m.* , a.ksdc,t.kscs,t.Ksdc 
from m , a , b t
where m.nsid = a.nsid and m.nsid = t.nsid and not exists (select 1 from b where nsid = t.nsid and kscs > t.kscs)

[仙侣步惊云]

select M.nsid,M.xm,M.school,A.ksdc,B.kscs,B.Ksdc
from tbjbxx M,tbksone A,tbzk,
(select nsid,max(kscs) from tbzk group by nsid) C
where M.nsid=A.nsid and A.nsid=B.nsid and B.nsid=C.nsid and B.kscs=C.kscs

出现出错提示:Derived table 'C' has no name for column 2

[suiziguo]

max(kscs)需要别名


select nsid,max(kscs) kscs from tbzk group by nsid

[suiziguo]

select M.nsid,M.xm,M.school,A.ksdc,B.kscs,B.Ksdc
from tbjbxx M,tbksone A,tbzk B,
(select nsid,max(kscs) kscs from tbzk group by nsid) C
where M.nsid=A.nsid and A.nsid=B.nsid and B.nsid=C.nsid and B.kscs=C.kscs;


这样试哈子。

[仙侣步惊云]

select M.nsid,M.xm,M.school,A.ksdc,B.kscs,B.Ksdc
from tbjbxx M,tbksone A,tbzk B,
(select nsid,max(kscs) kscs from tbzk B group by nsid) C
where M.nsid=A.nsid and A.nsid=B.nsid and B.nsid=C.nsid and B.kscs=C.kscs
to:GuidingStar坚挺旗舰)您B中的kscs和ksdc不是从kscs最大的行中取的,能保证结果正确吗?我再试试

现在看看爱新觉罗.毓华的语句

[suiziguo]

C里面是最大行，B和C关联，当然可以取出。你看看实际数据。

[仙侣步惊云]

两位大师的语句都是正确的，稍后结帐

[tangren]

--还是写一个
SELECT M.nsid, M.xm, M.school, A.ksdc, B.kscs, B.Ksdc
  FROM m, a, (SELECT b.*, row_number() over(PARTITION BY nsid ORDER BY kscs DESC) rn FROM b) c
 WHERE m.nsid = a.nsid AND
       m.nsid = c.nsid AND
       c.rn = 1;

[tangren]

--上面别名有点小误
SELECT M.nsid, M.xm, M.school, A.ksdc, c.kscs, c.Ksdc
FROM m, a, (SELECT b.*, row_number() over(PARTITION BY nsid ORDER BY kscs DESC) rn FROM b) c
WHERE m.nsid = a.nsid AND
m.nsid = c.nsid AND
c.rn = 1;