多表联系关系，去除重复数据
日期：2014-05-17 浏览次数：20661 次
多表关联，去除重复数据
sql2000
语句：
a表 c表
a.c_id c.r_id c.c_id
1103 22 1104
1104 23 1104
1120 24 1104
a表主键在c表中对应多条记录，我想只取关联后的最大一条或者第一条（有个限定条件最好）
------解决方案--------------------
select *
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'
------解决方案--------------------
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 1104
a表主键在c表中对应多条记录，我想只取关联后的最大一条或者第一条（有个限定条件最好）
------解决方案--------------------
select *
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'
------解决方案--------------------
select *
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'
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
