A表
ID Content
1 A
2 B
3 C
B表
TypeID Content ID
1 A 1
1 B 2
2 C 3
2 D 4
3 E 5 A连B表 最后结果要求1 A 1 A 2
2 B 2 D 4
3 C 3 E 5其中 A表的ID 对应B表的TypeID
B表只要B.ID最大的一行谢谢啦
ID Content
1 A
2 B
3 C
B表
TypeID Content ID
1 A 1
1 B 2
2 C 3
2 D 4
3 E 5 A连B表 最后结果要求1 A 1 A 2
2 B 2 D 4
3 C 3 E 5其中 A表的ID 对应B表的TypeID
B表只要B.ID最大的一行谢谢啦
insert into a values(1 ,'A')
insert into a values(2 ,'B')
insert into a values(3 ,'C')
create table b(TypeID int, Content varchar(10),ID int)
insert into b values(1, 'A', 1)
insert into b values(1, 'B', 2)
insert into b values(2, 'C', 3)
insert into b values(2, 'D', 4)
insert into b values(3, 'E', 5)
goselect a.* , t.* from a , b t where a.id = t.typeid and t.id = (select max(id) from b where TypeID = t.TypeID)select a.* , t.* from a , b t where a.id = t.typeid and not exists (select 1 from b where TypeID = t.TypeID and id > t.id)drop table a , b/*ID Content TypeID Content ID
----------- ---------- ----------- ---------- -----------
1 A 1 B 2
2 B 2 D 4
3 C 3 E 5(所影响的行数为 3 行)ID Content TypeID Content ID
----------- ---------- ----------- ---------- -----------
1 A 1 B 2
2 B 2 D 4
3 C 3 E 5(所影响的行数为 3 行)
*/
应该是B吧。dawugui的这个???
*
from
a , b t
where
a.id = t.typeid
and
t.id = (select max(id) from b where TypeID = t.TypeID)
create table A(ID int,Content varchar(10))
insert into a values(1 ,'A')
insert into a values(2 ,'B')
insert into a values(3 ,'C')
create table b(TypeID int, Content varchar(10),ID int)
insert into b values(1, 'A', 1)
insert into b values(1, 'B', 2)
insert into b values(2, 'C', 3)
insert into b values(2, 'D', 4)
insert into b values(3, 'E', 5)
go
select id,content,bcontent,bid from (
select a.*,b.content bcontent,b.id as bid ,max(b.id) over( partition by a.id) as max_id
from A,b where a.id=b.typeid
) a where bid=max_id id content bcontent bid
----------- ---------- ---------- -----------
1 A B 2
2 B D 4
3 C E 5(3 行受影响)