sql语句去重查询（跪请大神帮忙。。。）

[CrystalAngelLee]

sql语句：
“select p.pid,p.title,p.subtitle,p.price,p.href,m.standard_pic from yc_product p,yc_product_pic m where p.pid=m.pid and p.tid=1”

每一个商品（对应每一个pid）对应有不同的图片，但是不在同一张表中，想要从两张表中都查询出东西并且每个商品只想保留一张图片（只显示一个 standard_pid），用过distinct 和 group by 都不行
大神们帮帮忙

[学海无涯-回头是岸]

groupby 除开picture 那个字段，select picture聚合下

[Secends_]

引用 3 楼 Secends_ 的回复:

with mm as( select pid,title,subtitle,price,href,standard_pic row_number()over(partition by pid order by standard_pic )rn from (select p.pid,p.title,p.subtitle,p.price,p.href,m.standard_pic from yc_product p,yc_product_pic m where p.pid=m.pid and p.tid=1 ) ) select * from mm where rn=1 你试试 看看行不行 是不是 你想要的

刚才没注意你用了m了 with mm换掉

[Secends_]

with m as( select pid,title,subtitle,price,href,standard_pic row_number()over(partition by pid order by standard_pic )rn from (select p.pid,p.title,p.subtitle,p.price,p.href,m.standard_pic from yc_product p,yc_product_pic m where p.pid=m.pid and p.tid=1 ) ) select * from m where rn=1 你试试 看看行不行 是不是 你想要的

[吉普赛的歌]

SELECT p.pid,
       p.title,
       p.subtitle,
       p.price,
       p.href,
       (SELECT TOP 1 m.standard_pic FROM yc_product_pic AS m WHERE p.pid=m.pid) AS standard_pic
FROM   yc_product AS p
WHERE  p.tid = 1

[道素]

你可以这样试试，和楼上的思路差不多

      select p.pid,p.title,p.subtitle,p.price,p.href,m.standard_pic 
      FROM yc_product p
      LEFT JOIN (
           SELECT ROW_NUMBER()OVER(PARTITION BY m.pid ORDER BY standard_pic) AS seq,* FROM yc_product_pic m
      ) AS mm ON mm.seq=1 AND mm.pid=p.pid