;WITH cte AS(
SELECT a.*,b.ORDER,b.username,b.begindate,b.enddate,b.status FROM products a LEFT JOIN ORDER b ON a.productid=b.productid
WHERE b.username='张三'
)
SELECT * FROM cte a WHERE NOT EXISTS(SELECT 1 FROM cte b WHERE a.productid=b.productid a.ORDER<b.ORDER )
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,'知音'
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
----测试
SELECT t.*,b.orderid, b.username, b.begindate, b.enddate, b.status
FROM [#Products] AS t LEFT OUTER JOIN
(SELECT c.*
FROM [#order] AS c INNER JOIN
(SELECT productid, MAX(begindate) AS begindate
FROM [#ORDER]
WHERE (username = '张三')
GROUP BY productid) AS k ON c.productid = k.productid AND c.begindate = k.begindate
WHERE c.username = '张三') AS b ON t.productid =b.ProductID
----> 测试数据：#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
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 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
WHERE UserName ='张三'
UNION
--查找没订阅的数据
SELECT a.ProductID, a.ProductName , Orderid, UserName , [BeginDate] , EndDate,[Status]
FROM #Products a LEFT JOIN #Order b ON a.ProductID=b.ProductID
WHERE OrderID IS NULL
/*
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
4 新华日报 NULL NULL NULL NULL NULL
5 知音 NULL NULL NULL NULL NULL
(4 行受影响)
*/
----> 测试数据：#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 行受影响)
*/
