sql过滤重复记录

[aigoo]

表1如下

<table width="200" border="1">
  <tr>
    <td>id</td>
    <td>userid</td>
    <td>bookid</td>
  </tr>
  <tr>
    <td>1</td>
    <td>1</td>
    <td>3</td>
  </tr>
  <tr>
    <td>2</td>
    <td>2</td>
    <td>4</td>
  </tr>
  <tr>
    <td>3</td>
    <td>3</td>
    <td>5</td>
  </tr>
  <tr>
    <td>4</td>
    <td>3</td>
    <td>5</td>
  </tr>
  <tr>
    <td>4</td>
    <td>2</td>
    <td>5</td>
  </tr>
</table>

表2如下:

<table width="200" border="1">
  <tr>
    <td>Url</td>
    <td>bookid</td>
  </tr>
  <tr>
    <td>defaultPic1.jpg</td>
    <td>3</td>
  </tr>
  <tr>
    <td>defaultPic2.jpg</td>
    <td>4</td>
  </tr>
  <tr>
    <td height="21">defaultPic3.jpg</td>
    <td>5</td>
  </tr>
  <tr>
    <td>defaultPic4.jpg</td>
    <td>5</td>
  </tr>
  <tr>
    <td>defaultPic5.jpg</td>
    <td>5</td>
  </tr>
</table>

最终得到的结果为

<table width="200" border="1">
  <tr>
    <td>url</td>
    <td>userid</td>
    <td>bookid</td>
  </tr>
  <tr>
    <td>defaultPic1</td>
    <td>1</td>
    <td>3</td>
  </tr>
  <tr>
    <td>defaultPic2</td>
    <td>2</td>
    <td>4</td>
  </tr>
  <tr>
    <td>defaultPic3</td>
    <td>3</td>
    <td>5</td>
  </tr>
</table>

请问怎么样写SQL才能得到最终的结果;

[lisir474465915]

期待答案

[aigoo]

表1如下
id userid bookid
1 1 3
2 2 4
3 3 5
4 3 5
4 2 5
表2如下:
Url bookid
defaultPic1.jpg 3
defaultPic2.jpg 4
defaultPic3.jpg 5
defaultPic4.jpg 5
defaultPic5.jpg 5
最终得到的结果为
url userid bookid
defaultPic1 1 3
defaultPic2 2 4
defaultPic3 3 5
请问怎么样写SQL才能得到最终的结果;

[CutBug]

declare @tbl1 table(id int ,userid int ,bookid int )
insert into @tbl1
select 1,        1,            3  union all
select 2,        2,            4  union all
select 3,        3,            5  union all
select 4,        3,            5  union all
select 4,        2,            5 
declare @tbl2 table(url varchar(50),bookid int )
insert into @tbl2
select 'defaultPic1.jpg',      3  union all
select 'defaultPic2.jpg',     4  union all
select 'defaultPic3.jpg',      5  union all
select 'defaultPic4.jpg',      5  union all
select 'defaultPic5.jpg',      5 

select d.url,c.userid,c.bookid from (
	select userid,bookid from @tbl1 a 
	 where not exists(
	select * from @tbl1 where userid<>a.userid and id=a.id 
	)
) c
join (
	select url,bookid from @tbl2 b
	where not exists(
	select * from @tbl2 where bookid=b.bookid and url>b.url 
	)
) d on d.bookid=c.bookid

/*输出结果
url                                                userid      bookid
-------------------------------------------------- ----------- -----------
defaultPic1.jpg                                    1           3
defaultPic2.jpg                                    2           4
defaultPic5.jpg                                    3           5
*/

[CutBug]

改一下

select d.url,c.userid,c.bookid from (
	select userid,bookid from @tbl1 a 
	 where not exists(
	select * from @tbl1 where userid<>a.userid and id=a.id 
	)
) c
join (
	select url,bookid from @tbl2 b
	where not exists(
	select * from @tbl2 where bookid=b.bookid and url<b.url 
	)
) d on d.bookid=c.bookid
/*返回:
url                                                userid      bookid
-------------------------------------------------- ----------- -----------
defaultPic1.jpg                                    1           3
defaultPic2.jpg                                    2           4
defaultPic3.jpg                                    3           5
*/

[zzxap]

[code=SQL]
看我的
select b.url ,a.userid,a.bookid
from
(
select * from table1 a where exists (select top 1 bookid from table1 where bookid=a.bookid)
)b left join table2 b on a.bookid=a.bookid
[/CODE]

[zzxap]

[code=SQL]
改了
select b.url ,a.userid,a.bookid
from
(
select * from table1 c where exists (select top 1 bookid from table1 where bookid=c.bookid)
)a left join table2 b on a.bookid=b.bookid
[/CODE]

[che2piaopiao]

Distinct

[阿非]

declare @tableOne table([id] int,[userid] int,[bookid] int) 
declare @tableTwo table([url] varchar(20),[bookid] int)

insert into @tableOne select 1,1,3
union all select 2,2,4
union all select 3,3,5
union all select 4,3,5
union all select 4,2,5

select * from @tableOne

insert into @tableTwo select 'default.Pic1.jpg',3
union all select 'default.Pic2.jpg',4
union all select 'default.Pic3.jpg',5
union all select 'default.Pic4.jpg',5
union all select 'default.Pic5.jpg',5

select * from @tableTwo

select two.[url],one.[userid],one.[bookid] 
from (select * from @tableOne a where [id]=(select min([id]) from @tableOne where [userid]=a.[userid] )) one 
inner join (select  [bookid],min([url]) url from @tableTwo group by [bookid]) two
on one.[bookid]=two.[bookid]

[zzxap]

3 3 5
4 3 5
4 2 5

关键是这三条数据你想要那一条？

[aigoo]

问题解决 谢谢大家,结貼

[lxmtx___2008]

up