2表查询取唯一问题
pnews表
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
pnewsid title source appenddate
121231232007-01-26 17:14:48.000
1312中心2007-01-26 00:00:00.000
1411112007-01-26 00:00:00.000
1511中心2007-01-26 00:00:00.000
1611中心2007-01-26 00:00:00.000
171212中心2007-01-26 00:00:00.000
181212中心2007-01-26 00:00:00.000
191212中心2007-01-26 00:00:00.000
20323中心2007-01-26 00:00:00.000
211··1中心2007-01-26 00:00:00.000
pictures表
-----------------------------------
pid pnewsid pname pintr place
2112noimage.gif 1
2212noimage.gif无介绍 0
2313noimage.gif无介绍 0
24144ced4.gif 上铺image 1
2514f58f0f.jpg iyang 2
2614adbfe4.jpgyou yige taiyang 3
27144ced4.gif 1 0
28144ced4.gif 2 0
29144ced4.gif 3 0
30144ced4.gif 4 0
31144ced4.gif 5 0
-----------------------
我现在想要的结果就是
pictures表里头去掉pnewsid这个字段重复的记录，然后跟pnews表里头的title字段的组合
形式如：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
pid pnewsid title pname
2212 123 noimage.gif
2313 12 noimage.gif
2414 111 4ced4.gif
万分感谢！！！
[解决办法]
select distinct pid , pnewsid , title , pname
from (select ... from pictures,pnews where pnews.pnewsid=pictures.pnewsid) a
[解决办法]
select m.* , n.* from pnews m,
(
select a.* from pictures a,
(select pid , min(pnewsid) as pnewsid from pictures group by pid) b
where a.pid = b.pid and a.pnewsid = b.pnewsid
) n
where m.pnewsid = n.pnewsid
[解决办法]
select
pid=max(pid),
pnewsid,
title=(select title from pnews where pnewsid=tmp.pnewsid),
pname=max(pname)
from pictures as tmp
group by pnewsid
--result
pid pnewsid title pname
----------- ----------- ---------- --------------------
22 12 123 noimage.gif
23 13 12 noimage.gif
31 14 11 f58f0f.jpg
(3 row(s) affected)
