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
select p.ProductID,p.Name,max(o.OrderId),max(o.OrderDate) from @Products p left join @Orders o on p.ProductID=o.ProductId group by p.ProductID,p.Name
你这个属于求 最离当前时间最近的一条记录 SELECT * FROM 已知表 AS 表 INNER JOIN ( SELECT id,max(日期字段) as 最大日期 FROM 已知表 GROUP BY id ) AS T ON T.id = 表.id AND 表.日期 = t.最大日期 楼主的问题 我也遇到过 我提供你1个 我的解决方案吧 一起分享下!
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,max(orderdate)'OrderDate' from Products a left join Orders b on a.ProductId =b.ProductId group by a.ProductId,name
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
SELECT * FROM 已知表 AS 表
INNER JOIN
(
SELECT id,max(日期字段) as 最大日期 FROM 已知表
GROUP BY id
) AS T ON T.id = 表.id AND 表.日期 = t.最大日期 楼主的问题 我也遇到过 我提供你1个 我的解决方案吧 一起分享下!
看这一行就知道不能用inner join
要用外连接1,2楼的应该没问题
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
on a.ProductId =b.ProductId
group by a.ProductId,name