这次应该没问题了。那个参数@username是你传入的值，如果不需要筛选就穿null
----> 测试数据：#Products
-- IF OBJECT_ID('TEMPDB.DBO.#Products') IS NOT NULL DROP TABLE #Products
-- GO
-- CREATE TABLE #Products([ProductID] INT,[ProductName] VARCHAR(8))
-- INSERT #Products
-- SELECT 1,'故事会' UNION ALL
-- SELECT 2,'青年文摘' UNION ALL
-- SELECT 3,'英语月刊' UNION ALL
-- SELECT 4,'新华日报' UNION ALL
-- SELECT 5,'知音'
-- --------------开始查询--------------------------
-- --> 测试数据：#Order
-- IF OBJECT_ID('TEMPDB.DBO.#Order') IS NOT NULL DROP TABLE #Order
-- GO
-- CREATE TABLE #Order([OrderID] INT,[ProductID] INT,[UserName] VARCHAR(4),[BeginDate] DATETIME,[EndDate] DATETIME,[Status] INT)
-- INSERT #Order
-- SELECT 1,1,'张三','2012-01-02','2012-05-02',1 UNION ALL
-- SELECT 2,1,'张三','2012-06-02','2013-12-31',0 UNION ALL
-- SELECT 3,2,'张三','2012-01-01','2012-05-01',1 UNION ALL
-- SELECT 4,3,'李四','2012-01-01','2014-01-01',0 UNION ALL
-- SELECT 5,5,'李四','2012-01-01','2014-01-01',1
DECLARE @username NVARCHAR(128)
SET @username=NULL
SELECT a.ProductID, a.ProductName , Orderid, UserName , [BeginDate] , EndDate,[Status]
FROM #Products a LEFT JOIN
(SELECT * FROM #Order b WHERE EXISTS
(SELECT 1 FROM
( SELECT MAX(begindate)begindate,productid, username FROM #Order WHERE username=ISNULL(@username,username) GROUP BY productid, username) c WHERE b.begindate=c.begindate AND b.productid=c.productid AND b.username=c.username ))b
ON a.ProductID=b.ProductID
/*
ProductID ProductName Orderid UserName BeginDate EndDate Status
----------- ----------- ----------- -------- ----------------------- ----------------------- -----------
1 故事会 2 张三 2012-06-02 00:00:00.000 2013-12-31 00:00:00.000 0
2 青年文摘 3 张三 2012-01-01 00:00:00.000 2012-05-01 00:00:00.000 1
3 英语月刊 4 李四 2012-01-01 00:00:00.000 2014-01-01 00:00:00.000 0
4 新华日报 NULL NULL NULL NULL NULL
5 知音 5 李四 2012-01-01 00:00:00.000 2014-01-01 00:00:00.000 1
(5 行受影响)
*/
