问一个Join的问题

Question

现在有两涨表：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  如有多个订阅，则取最后订阅的产品数据。   望指教刚才手贱，还没解决就给点了结贴，重新发一份。原帖地址：http://bbs.csdn.net/topics/390271298

Answer 1

我想知道张三原有那3条数据，变成2条，是因为status？还是日期？

Answer 2

;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  )不知道行不。

Answer 3

 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

Answer 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
 
 
 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 行受影响)
 
 
 */

Answer 5

这次应该没问题了。那个参数@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 行受影响)
 */

Answer 6

过滤order表去相同Product时间最晚的，在用Product左连接