＋设为首页 ＋添加到收藏夹
我要查询两个表：table1,table2.
table1的主键是prikey1,prikey2;table2的主键是prikey1，prikey2,prikey3,并且prikey2为int 型.我要查的是table1的prikey1等于table2的prikey1,table1的prikey2等于table2的prikey2,同时prikey2在table1中是最大的。
prikey2在table1中是最大的,还差个条件啊，pk2是按pk1 group 呢还是什么？
select max(a.prikey2) from table1 a inner join table2
on a.prikey1=b.prikey1 and a.prikey2=b.prikey2
题目不清，举个例子！
use pubs
create table table1(prikey1 int, prikey2 int, primary key(prikey1,prikey2))
insert table1 values(1, 3)
insert table1 values(1, 4)
insert table1 values(2, 5)
insert table1 values(2, 6)
insert table1 values(2, 7)
create table table2(prikey1 int, prikey2 int, prikey3 int, primary key(prikey1,prikey2,prikey3))
insert table2 values(1, 4, 1)
insert table2 values(1, 1, 2)
insert table2 values(2, 4, 3)
insert table2 values(2, 7, 4)
insert table2 values(4, 1, 5)
select a.*, b.* from table1 a
inner join table2 b on a.prikey1 = b.prikey1 and a.prikey2 = b.prikey2
where a.prikey2 = (select max(c.prikey2) from table1 c where c.prikey1 = a.prikey1 group by c.prikey1)
up
