sql2000
语句:
select * from tb_c_info as a inner join tb_c_attrib as b on a.c_id=b.c_id inner join tb_c_o_record as c on a.c_id=c.c_id where c.r_file like '%.avi' a表 c表
a.c_id c.r_id c.c_id
1103 22 1104
1104 23 1104
1120 24 1104a表主键在c表中对应多条记录,我想只取关联后的最大一条或者第一条(有个限定条件最好)
语句:
select * from tb_c_info as a inner join tb_c_attrib as b on a.c_id=b.c_id inner join tb_c_o_record as c on a.c_id=c.c_id where c.r_file like '%.avi' a表 c表
a.c_id c.r_id c.c_id
1103 22 1104
1104 23 1104
1120 24 1104a表主键在c表中对应多条记录,我想只取关联后的最大一条或者第一条(有个限定条件最好)
from tb_c_info as a inner join tb_c_attrib as b on a.c_id=b.c_id
inner join (select MAX(r_id)r_id,c_id FROM tb_c_o_record GROUP BY c_id) as c on a.c_id=c.c_id
where c.r_file like '%.avi'
go
create table #tb_c_info(c_id varchar(100));
insert #tb_c_info
select '1103' union all
select '1104' union all
select '1120' if OBJECT_ID('tempdb..#tb_c_attrib', 'u') is not null drop table #tb_c_attrib;
go
create table #tb_c_attrib(c_id int);
insert #tb_c_attrib
select '1103' union all
select '1104' union all
select '1120' if OBJECT_ID('tempdb..#tb_c_o_record', 'u') is not null drop table #tb_c_o_record;
go
create table #tb_c_o_record( r_id int, c_id int, r_file VARCHAR(100));
insert #tb_c_o_record
select '22','1104','a.avi' union all
select '23','1104','b.avi' union all
select '24','1104','c.avi'--SQL:--select * from #tb_c_info
--SELECT * FROM #tb_c_attrib
--select * from #tb_c_o_recordSELECT a.*,b.*,d.*
FROM #tb_c_info AS a
INNER JOIN #tb_c_attrib AS b
ON a.c_id = b.c_id
LEFT JOIN (SELECT r_id=MIN(r_id), c_id FROM #tb_c_o_record GROUP BY c_id) AS c
ON a.c_id = c.c_id
LEFT JOIN #tb_c_o_record d
ON c.c_id = d.c_id
AND c.r_id = d.r_id
AND d.r_file LIKE '%.avi'
/*
c_id c_id r_id c_id r_file
1103 1103 NULL NULL NULL
1104 1104 22 1104 a.avi
1120 1120 NULL NULL NULL
*/
from tb_c_info as a
inner join tb_c_attrib as b on a.c_id=b.c_id
inner join
(select t1.*
from tb_c_o_record t1
inner join (select MAX(r_id) r_id,c_id FROM tb_c_o_record GROUP BY c_id)t2
on t1.r_id=t2.r_id and t1.c_id=t2.c_id
)c on a.c_id=c.c_id
where c.r_file like '%.avi'
可能我说的不够清楚
a表 b表 c表
a.c_id b.b_id b.c_id c.r_id c.c_id
1103 8 1103 22 1103
1104 9 1104 23 1104
1104 10 1104 24 1104
1104 11 1104 25 1104
1104 12 1104 26 1104
1120 13 1120 27 1120
现在是想去掉1104这个重复的只取b.b_id或者c.r_id为最大或者最小的记录
SELECT a.*,b.*,d.*
FROM #tb_c_info AS a
LEFT JOIN (SELECT b_id=MIN(b_id), c_id FROM #tb_c_attrib GROUP BY c_id) AS b
ON a.c_id = b.c_id
LEFT JOIN (SELECT r_id=MIN(r_id), c_id FROM #tb_c_o_record GROUP BY c_id) AS c
ON a.c_id = c.c_id
LEFT JOIN #tb_c_o_record d
ON c.c_id = d.c_id
AND c.r_id = d.r_id
AND d.r_file LIKE '%.avi'