问一个Join的问题

[倒大霉的上帝]

现在有两涨表：Products表：
ProductID ProductName
1 故事会
2 青年文摘
3 英语月刊
4 新华日报
5 知音

Order表
OrderID ProductID UserName BeginDate EndDate Status
1 1 张三 2012-01-02 2012-05-02 1
2 1 张三 2012-06-02 2013-12-31 0
3 2 张三 2012-01-01 2012-05-01 1
4 3 李四 2012-01-01 2014-01-01 0

我现在想通过Products表来Left Join 条件是根据单个用户，比如张三 得到如下结果
ProductID ProductName Order UserName BeignDate EndDate Status
1 故事会 2 张三 2012-6-02 2013-12-31 0
2 青年文摘 3 张三 2012-1-1 2012-5-1 1
3 英语月刊 null null null null null
4 新华日报 null null null null null
5 知音 null null null null null

根据所有产品及用户进行查询，产品要涵盖全，如未订阅，则订阅数据为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
--------------开始查询--------------------------

SELECT * FROM #Products p LEFT JOIN  #Order  AS o 
ON p.[ProductID]=o.[ProductID]
WHERE NOT EXISTS (SELECT 1 FROM  #Order  WHERE [ProductID]=o.[ProductID] AND [BeginDate]>o.[BeginDate])
----------------结果----------------------------
/* 
ProductID	ProductName	OrderID	ProductID	UserName	BeginDate	EndDate	Status
1	故事会	2	1	张三	2012-06-02 00:00:00.000	2013-12-31 00:00:00.000	0
2	青年文摘	3	2	张三	2012-01-01 00:00:00.000	2012-05-01 00:00:00.000	1
3	英语月刊	4	3	李四	2012-01-01 00:00:00.000	2014-01-01 00:00:00.000	0
4	新华日报	NULL	NULL	NULL	NULL	NULL	NULL
5	知音	NULL	NULL	NULL	NULL	NULL	NULL
*/

[發糞塗牆]

你都知道要干什么了，还问？

[黄_瓜]

引用 1 楼 Beirut 的回复:

SQL code1234567891011121314151617181920212223242526272829303132333435--> 测试数据：#ProductsIF OBJECT_ID('TEMPDB.DBO.#Products') IS NOT NULL DROP TABLE #ProductsGO CREATE TABLE #Products([Prod……

搞错了

[开启时代]

select A.*,b.orderid ,b.username,b.begindate,b.enddate,b.status
from PRODUCT as a left join (
select * from order as c 
inner join (select productid,MAX(begindate) AS begindate from ORDER where username='张三' group by productid) as d 
on c.productid=d.productid and c.begindate=d.begindate)
where c.username='张三'
) as b 
on a.productid=b.prodcutid

[發糞塗牆]

汤哥，别急，这题我不做就是拉

[黄_瓜]

--似乎是对的，在加上姓名过滤

SELECT * FROM #Products p LEFT JOIN  #Order  AS o 
ON p.[ProductID]=o.[ProductID]
WHERE NOT EXISTS (SELECT 1 FROM  #Order  WHERE [ProductID]=o.[ProductID] AND [BeginDate]>o.[BeginDate])
AND O.ProductName='张三'

[倒大霉的上帝]

引用 5 楼 Beirut 的回复:

SQL code123456--似乎是对的，在加上姓名过滤 SELECT * FROM #Products p LEFT JOIN #Order AS o ON p.[ProductID]=o.[ProductID]WHERE NOT EXISTS (SELECT 1 FROM #Order WHERE [ProductID]=o.[ProductID] AND ……

加上姓名过滤的话，就会把其他没订阅的Product过滤掉了

[dd_2012]

   
   select *
   from Products a 
   left join [Order] b 
   on a.ProductID=b.ProductID
   and not exists(select 1 from [Order] c where c.ProductID=c.ProductID
                  and c.OrderID>b.OrderID)

[倒大霉的上帝]

引用 2 楼 DBA_Huangzj 的回复:

你都知道要干什么了，还问？

知道要干什么只能说是需求明确了。哈哈。至于怎么做还需要大牛们指点啊

[LongRui888]

引用上面的建表语句，下面的查询做了修改：

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 * 
FROM #Products p 
LEFT JOIN  
(
	SELECT o.*
	FROM #Order o
	INNER JOIN 
		(
			SELECT o.ProductID,
			       max(BeginDate) AS BeginDate
			FROM #Order o
			WHERE 1=1
			      AND o.UserName = '张三'  
			GROUP BY o.ProductID
		)oo
		
		ON oo.ProductID = o.ProductID
		   AND oo.BeginDate  = o.BeginDate
)o
ON o.ProductID = p.ProductID

[LongRui888]

上面的not exists 其实类似于inner join是直接过滤，会导致没有被订阅的产品显示不出来