日期:2014-05-18  浏览次数:20554 次

请问怎么写SQL?
数据库结构
表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 n
where m.detailsid = n.detailsid and
n.chrimage = (select top 1 chrimage from tb2 where liveid = n.liveid order by chrimage)

select m.* , n.* from tb1 m , tb2 n
where m.detailsid = n.detailsid and
n.chrimage = (select top 1 chrimage from tb2 where liveid = n.liveid order by chrimage desc)

select m.* , n.* from tb1 m , tb2 n
where m.detailsid = n.detailsid and
n.chrimage = (select min(chrimage) from tb2 where liveid = n.liveid)

select m.* , n.* from tb1 m , tb2 n
where m.detailsid = n.detailsid and
n.chrimage = (select max(chrimage) from tb2 where liveid = n.liveid)

select m.* , n.* from tb1 m , tb2 n
where m.detailsid = n.detailsid and
not exists (select 1 from tb2 where liveid = n.liveid and chrimage < n.chrimage)

select m.* , n.* from tb1 m , tb2 n
where m.detailsid = n.detailsid and
not exists (select 1 from tb2 where liveid = n.liveid and chrimage > n.chrimage)