数据库结构
表1字段
detailsid chrtitle
1 标题1
2 标题2
3 标题3
4 标题4
表2字段
chrimage liveid
1.jpg 1
2.jpg 1
3.jpg 1
4.jpg 2
5.jpg 3
6.jpg 4
查询出来结果 表2的chrimage不允许有重复
------解决方案--------------------
- SQL code
select m.* , n.* from tb1 m , tb2 nwhere m.detailsid = n.detailsid andn.chrimage = (select top 1 chrimage from tb2 where liveid = n.liveid order by chrimage)select m.* , n.* from tb1 m , tb2 nwhere m.detailsid = n.detailsid andn.chrimage = (select top 1 chrimage from tb2 where liveid = n.liveid order by chrimage desc)select m.* , n.* from tb1 m , tb2 nwhere m.detailsid = n.detailsid andn.chrimage = (select min(chrimage) from tb2 where liveid = n.liveid)select m.* , n.* from tb1 m , tb2 nwhere m.detailsid = n.detailsid andn.chrimage = (select max(chrimage) from tb2 where liveid = n.liveid)select m.* , n.* from tb1 m , tb2 nwhere m.detailsid = n.detailsid andnot exists (select 1 from tb2 where liveid = n.liveid and chrimage < n.chrimage)select m.* , n.* from tb1 m , tb2 nwhere m.detailsid = n.detailsid andnot exists (select 1 from tb2 where liveid = n.liveid and chrimage > n.chrimage)