表Products: id brand name publishyear platform
1 Moto A3000 2008 windows
2 Moto A3100 2009 windows
3 HTC Hero 2009 android
4 Nokia E72 2009 symbian
表Price
id product_id user publishdate price
1 2 张三 12-10 2700.00
2 2 李四 12-9 2900.00
3 1 张三 12-9 1200.00
4 4 张三 12-11 1900.00
5 4 王五 12-11 1300.00 说明:两个表都以id为主键,表Prices中的product_id为外键,链接Products表的id字段,对应关系为Product.id:Prices.Product.id=1:N。
请提交一个SQL,返回同一产品最低报价数据,如下表:(语法请遵循T-SQL规范,注意没有报价的产品不显示,以及按照价格排序)
brand name user publishdate price
Moto A3100 张三 12-10 2700.00
Nokia E72 王五 12-11 1300.00
Moto A3000 张三 12-9 1200.00
from Products a inner join Price b on a.id=b.product_id
where not exists (select 1 from Price where product_id=b.product_id and price<b.price)
select a.brand,a.name,b.user,b.publishdate,b.price
from Products a inner join Price b on a.id=b.product_id
where b.price=(select min(price) from Price where product_id=b.product_id)select a.brand,a.name,b.user,b.publishdate,b.price
from Products a inner join Price b on a.id=b.product_id
inner join (select product_id, min(price) as price from Price group by product_id) c on b.product_id=c.product_id and b.price=c.price
--> author:Ken Wong
--> Add date:2009-12-13 11:37:33
/*=============================================*/
--> 测试数据:@Products
declare @Products table([id] int,[brand] varchar(5),[name] varchar(5),[publishyear] int,[platform] varchar(7))
insert @Products
select 1,'Moto','A3000',2008,'windows' union all
select 2,'Moto','A3100',2009,'windows' union all
select 3,'HTC','Hero',2009,'android' union all
select 4,'Nokia','E72',2009,'symbian'
--> 测试数据:@Price
declare @Price table([id] int,[product_id] int,[user] varchar(4),[publishdate] varchar(5),[price] numeric(6,2))
insert @Price
select 1,2,'张三','12-10',2700.00 union all
select 2,2,'李四','12-9',2900.00 union all
select 3,1,'张三','12-9',1200.00 union all
select 4,4,'张三','12-11',1900.00 union all
select 5,4,'王五','12-11',1300.00select r.[brand],r.[name],t.[user],t.[publishdate],t.[price]
from @Products r join @Price t
on r.[id] = t.[product_id]
where t.[price] = (select min([price]) from @Price where [product_id] = t.[product_id])
order by t.id
-----------------------------
Moto A3100 张三 12-10 2700.00
Moto A3000 张三 12-9 1200.00
Nokia E72 王五 12-11 1300.00
select a.brand,a.[name],b.[user],b.publishdate,b.price
from Products a,Price b
where a.id=b.product_id
and not exists(select 1 from Price c where c.product_id = a.id and b.price>c.price)