两张关联表，取重复记录中的一条
现有两张表produt（产品表),productImg(产品图片）
product字段如下
Id productName price
1 产品一 100
2 产品二 130
3 产品三 120
productImg字段如下
ImgId F_Id ImageUrl
1 2 images/1.jpg
2 2 images/2.jpg
3 2 images/4.jpg
4 3 images/33.jpg
5 3 images/331.jpg
通过
select min(ImgId),productName,Id from product left join productImg on product.Id = productImg.F_Id group by productName,Id
可以得到不重复的记录，但是我想同时取的price这个字段，改成如下写法就不生效了。
select min(ImgId),productName,Id,Price from product left join productImg on product.Id = productImg.F_Id group by productName,Id,Price
--------------------编程问答-------------------- 想得到这样的结果
Id productName price ImageUrl
1 产品一 100
2 产品二 130 images/1.jpg
3 产品三 120 images/33.jpg
--------------------编程问答-------------------- oracle 有个 rank() over 函数
mssql 你可以
select a.*,b.price from
(select min(ImgId),productName,Id from product left join productImg on product.Id = productImg.F_Id group by productName,Id ) a left join product b on a.id=b.id --------------------编程问答-------------------- select a.Id,a.productName,a.Price,a.ImgId,b.ImageUrl from (select Id,productName,price,min(ImgId) as ImgId from product left join productImg on product.Id = productImg.F_Id
group by Id,productName,price) a left join productImg b on a.ImgId = b.ImgId楼主试试这样可以吧 --------------------编程问答-------------------- 试试这样吧
select min(ImgId),productName,Id,min(prize) from product left join productImg on product.Id = productImg.F_Id group by productName,Id--------------------编程问答-------------------- select min(ImgId),productName,Id,min(price) from product left join productImg on product.Id = productImg.F_Id group by productName,Id
这样也不行 --------------------编程问答--------------------
;with product(Id,productName,price) as--------------------编程问答-------------------- 还有一种方法 ：
(
select 1,'产品一',100
union all select 2,'产品二',130
union all select 3,'产品三',120
),
productImg(ImgId,F_Id,ImageUrl) as
(
select 1,2,'images/1.jpg'
union all select 2,2,'images/2.jpg'
union all select 3,2,'images/4.jpg'
union all select 4,3,'images/33.jpg'
union all select 5,3,'images/331.jpg'
)
select a.*,b.ImageUrl
from product a
left join
(select t1.*
from productImg t1
inner join (select min(ImgId)as ImgId,F_Id from productImg group by F_Id)t2
on t1.F_Id=t2.F_Id and t1.ImgId=t2.ImgId
)b on a.Id=b.F_Id
/*
Id productName price ImageUrl
1 产品一 100 NULL
2 产品二 130 images/1.jpg
3 产品三 120 images/33.jpg
*/
;with product(Id,productName,price) as--------------------编程问答-------------------- 知道学习下
(
select 1,'产品一',100
union all select 2,'产品二',130
union all select 3,'产品三',120
),
productImg(ImgId,F_Id,ImageUrl) as
(
select 1,2,'images/1.jpg'
union all select 2,2,'images/2.jpg'
union all select 3,2,'images/4.jpg'
union all select 4,3,'images/33.jpg'
union all select 5,3,'images/331.jpg'
)
select a.*,b.ImageUrl
from product a
left join
(select t1.*
from productImg t1
where not exists(select 1 from productImg t2 where t1.F_Id=t2.F_Id and t1.ImageUrl>t2.ImageUrl)
)b on a.Id=b.F_Id
/*
Id productName price ImageUrl
1 产品一 100 NULL
2 产品二 130 images/1.jpg
3 产品三 120 images/33.jpg
*/
补充：.NET技术 , ASP.NET
