问一个Join的有关问题

2012-12-17

问一个Join的问题现在有两涨表：Products表：ProductIDProductName1故事会2青年文摘3英语月刊4新华日报5知音

问一个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  如有多个订阅，则取最后订阅的产品数据。

望指教

刚才手贱，还没解决就给点了结贴，重新发一份。

原帖地址：http://bbs.csdn.net/topics/390271298
[最优解释]
status需要筛选吗？
[其他解释]
我想知道张三原有那3条数据，变成2条，是因为status？还是日期？
[其他解释]



;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

[其他解释]

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

*/

[其他解释]

引用:

SQL code1234567891011121314151617181920212223242526272829303132333435363738394041424344454647----> 测试数据：#Products --IF OBJECT_ID('TEMPDB.DBO.#Products') IS NOT NULL DROP TABLE #Products -……

谢谢，张三的“英语月刊”还是没查出来呢？
[其他解释]

引用:

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

因为日期
[其他解释]

引用:

--查找没订阅的数据
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

这部分会有问题的，李四如果订阅了一个张三没订阅的数据，那么张三也是查不出来的。
[其他解释]
这次应该没问题了。那个参数@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 行受影响)
*/

[其他解释]

引用:

这次应该没问题了。那个参数@username是你传入的值，如果不需要筛选就穿nullSQL code12345678910111213141516171819202122232425262728293031323334353637383940414243----> 测试数据：#Products -- IF OBJECT_ID('TEMPDB.DBO.#Products')……

好长
看起来好眼花，我先研究下就结贴大侠好敬业啊，都快10点了还在奋斗
[其他解释]
过滤order表去相同Product时间最晚的，在用Product左连接
[其他解释]

引用:

这次应该没问题了。那个参数@username是你传入的值，如果不需要筛选就穿nullSQL code12345678910111213141516171819202122232425262728293031323334353637383940414243----> 测试数据：#Products -- IF OBJECT_ID('TEMPDB.DBO.#Products')……

大人乃神人也！

热点排行

SQL Server

问一个Join的有关问题