主表:tabMain(
id int,
name varchar(10)
)tabDetail(
sub_id int,
parentid int,
sub_name int,
sub_address varchar(10)
)tabMain
id name
--------------------
1 AA
2 BB
tabDetail
sub_id parentid sub_name sub_address
-------------------------------------------
1 1 A1 F
2 1 A2 G
3 1 A3 H
4 2 B2 J
5 2 B1 K
要的结果id name sub_id sub_name sub_address
---------------------------------------------------
1 AA 3 A3 H
2 BB 5 B1 K
返回 tblMain 记录 后面再左联接 tblDetail 记录
条件 tabDetail.parentid = tabMain.id and id = 最大的那一条3q
id int,
name varchar(10)
)tabDetail(
sub_id int,
parentid int,
sub_name int,
sub_address varchar(10)
)tabMain
id name
--------------------
1 AA
2 BB
tabDetail
sub_id parentid sub_name sub_address
-------------------------------------------
1 1 A1 F
2 1 A2 G
3 1 A3 H
4 2 B2 J
5 2 B1 K
要的结果id name sub_id sub_name sub_address
---------------------------------------------------
1 AA 3 A3 H
2 BB 5 B1 K
返回 tblMain 记录 后面再左联接 tblDetail 记录
条件 tabDetail.parentid = tabMain.id and id = 最大的那一条3q
select m.* from tabMain m,
(select t.* from tabDetail t where sub_id = (select max(sub_id) from tabDetail where parentid = t.parentid) ) n
where m.id = n.parentidselect m.* from tabMain m,
(select t.* from tabDetail t where not exists(select 1 from tabDetail where parentid = t.parentid and sub_id > t.sub_id) ) n
where m.id = n.parentid
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tabMain
DECLARE @tabMain TABLE (id INT,name VARCHAR(2))
INSERT INTO @tabMain
SELECT 1,'AA' UNION ALL
SELECT 2,'BB'
--> 生成测试数据: @tabDetail
DECLARE @tabDetail TABLE (sub_id INT,parentid INT,sub_name VARCHAR(2),sub_address VARCHAR(1))
INSERT INTO @tabDetail
SELECT 1,1,'A1','F' UNION ALL
SELECT 2,1,'A2','G' UNION ALL
SELECT 3,1,'A3','H' UNION ALL
SELECT 4,2,'B2','J' UNION ALL
SELECT 5,2,'B1','K'--SQL查询如下:SELECT *
FROM @tabMain AS A
JOIN @tabDetail AS B
ON A.ID=B.parentid
WHERE NOT EXISTS(
SELECT *
FROM @tabDetail
WHERE B.parentid=parentid
AND sub_id>B.sub_id
)/*
id name sub_id parentid sub_name sub_address
----------- ---- ----------- ----------- -------- -----------
1 AA 3 1 A3 H
2 BB 5 2 B1 K(2 行受影响)*/
from tabmain a,
(select parentid,sub_id,sub_name,sub_address from tabDetail t where not exists(select 1 from tabDetail where name=t.name and sub_id>t.sub_id) b
where a.id=b.parentid
create table tabDetail( sub_id int, parentid int, sub_name varchar(10), sub_address varchar(10) )
insert into tabMain values(1 , 'AA')
insert into tabMain values(2 , 'BB')
insert into tabDetail values(1 , 1 , 'A1' , 'F')
insert into tabDetail values(2 , 1 , 'A2' , 'G')
insert into tabDetail values(3 , 1 , 'A3' , 'H')
insert into tabDetail values(4 , 2 , 'B2' , 'J')
insert into tabDetail values(5 , 2 , 'B1' , 'K')
goselect m.* , n.sub_id,n.sub_name , n.sub_address from tabMain m,
(select t.* from tabDetail t where sub_id = (select max(sub_id) from tabDetail where parentid = t.parentid) ) n
where m.id = n.parentid
/*
id name sub_id sub_name sub_address
----------- ---------- ----------- ---------- -----------
1 AA 3 A3 H
2 BB 5 B1 K
*/select m.* ,n.sub_id,n.sub_name , n.sub_address from tabMain m,
(select t.* from tabDetail t where not exists(select 1 from tabDetail where parentid = t.parentid and sub_id > t.sub_id) ) n
where m.id = n.parentid
/*
id name sub_id sub_name sub_address
----------- ---------- ----------- ---------- -----------
1 AA 3 A3 H
2 BB 5 B1 K
*/
--drop table tabMain,tabDetail
create table tabDetail( sub_id int, parentid int, sub_name varchar(10), sub_address varchar(10) )
insert into tabMain values(1 , 'AA')
insert into tabMain values(2 , 'BB')
insert into tabDetail values(1 , 1 , 'A1' , 'F')
insert into tabDetail values(2 , 1 , 'A2' , 'G')
insert into tabDetail values(3 , 1 , 'A3' , 'H')
insert into tabDetail values(4 , 2 , 'B2' , 'J')
insert into tabDetail values(5 , 2 , 'B1' , 'K')
go
select a.id,a.name,b.sub_id,b.sub_name,b.sub_address
from tabmain a,
(select parentid,sub_id,sub_name,sub_address from tabDetail t where not exists(select 1 from tabDetail where parentid=t.parentid and sub_id>t.sub_id)) b
where a.id=b.parentidid name sub_id sub_name sub_address
----------- ---------- ----------- ---------- -----------
1 AA 3 A3 H
2 BB 5 B1 K(2 行受影响)
这里漏写了n.sub_id,n.sub_name , n.sub_address