34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE Prodcuts表([ProductId] INT,[Name] NVARCHAR(6))
INSERT Prodcuts表
SELECT 1,N'Cat' UNION ALL
SELECT 2,N'Dog' UNION ALL
SELECT 3,N'Rabbit'
CREATE TABLE Orders表([OrderId] INT,[ProductId] INT,[OrderDate] DATETIME)
INSERT Orders表
SELECT 1,1,'20110505' UNION ALL
SELECT 2,1,'20110506' UNION ALL
SELECT 3,1,'20110507' UNION ALL
SELECT 4,2,'20110505'
GO
SELECT
a.ProductId,
a.Name,
(SELECT TOP 1 b.OrderId FROM dbo.Orders表 b WHERE a.ProductId=b.ProductId ORDER BY OrderDate desc) AS orderid,
(SELECT TOP 1 b.OrderDate FROM dbo.Orders表 b WHERE a.ProductId=b.ProductId ORDER BY OrderDate desc) AS orderDate
FROM Prodcuts表 a
select a.ProductId,a.name,b.orderid,b.orderdate
from Prodcuts a left join
(select productid,max(orderid) as orderid,max(orderdate) as orderdate
from Orders group by productid) b on on a.ProductId=b.ProductId