请教怎样做内循环查询的问题
各位高人,表结构如下
[code=SQL][
USE TEST
GO 

CREATE TABLE #Test 
(
  CustID INT ,
  OrderID INT ,
  OrderDate DATETIME,
  Product CHAR(1)
)

INSERT #Test
SELECT 1,857,'2011-07-16 17:22:31.000','A' UNION ALL
SELECT 2,524,'2011-07-18 15:40:46.000','A' UNION ALL
SELECT 3,985,'2011-07-19 08:51:59.000','A' UNION ALL
SELECT 3,574,'2011-07-25 15:04:17.000','A' UNION ALL
SELECT 4,989,'2011-07-28 11:18:25.000','A' UNION ALL
SELECT 5,584,'2011-11-21 14:06:01.000','A' UNION ALL 
SELECT 6,547,'2011-11-22 11:51:54.000','A'

]



目的:查询购买同一个product,并且两个不同的custID购买时时间间隔在24小时内的数据

想得到的结果集如下:

CustID, OrderID, OrderDate, Product
2 524 '2011-07-18 15:40:46.000' 'A'
3 985 '2011-07-19 08:51:59.000' 'A'
5 584 '2011-11-21 14:06:01.000' 'A'
6 547 '2011-11-22 11:51:54.000' 'A'

多谢各位.





------解决方案--------------------


CREATE TABLE #Test
(
CustID INT ,
OrderID INT ,
OrderDate DATETIME,
Product CHAR(1)
)

INSERT #Test
SELECT 1,857,'2011-07-16 17:22:31.000','A' UNION ALL
SELECT 2,524,'2011-07-18 15:40:46.000','A' UNION ALL
SELECT 3,985,'2011-07-19 08:51:59.000','A' UNION ALL
SELECT 3,574,'2011-07-25 15:04:17.000','A' UNION ALL
SELECT 4,989,'2011-07-28 11:18:25.000','A' UNION ALL
SELECT 5,584,'2011-11-21 14:06:01.000','A' UNION ALL
SELECT 6,547,'2011-11-22 11:51:54.000','A'



select * from #Test



--:查询购买同一个product,并且两个不同的custID购买时时间间隔在24小时内的数据

--想得到的结果集如下:

--CustID, OrderID, OrderDate, Product
--2 524 '2011-07-18 15:40:46.000' 'A'
--3 985 '2011-07-19 08:51:59.000' 'A'
--5 584 '2011-11-21 14:06:01.000' 'A'
--6 547 '2011-11-22 11:51:54.000' 'A'


select A.custid,A.orderdate,A.OrderID,A.Product
from
(select ROW_NUMBER() over(order by orderdate) as No,*
from #Test) A
inner join (select ROW_NUMBER() over(order by orderdate) as No,*
from #Test)B on A.Product = B.Product 
and A.CustID <>B.custid and A.no+1=B.no
where DATEDIFF(HH,A.OrderDate,B.OrderDate) <=24


/*
2 2011-07-18 15:40:46.000 524 A
5 2011-11-21 14:06:01.000 584 A
------解决方案--------------------
custid =6 这行数据没有参考的后续值，所以没有选出来.

------解决方案--------------------

SQL code

CREATE TABLE #Test  
(
  CustID INT ,
  OrderID INT ,
  OrderDate DATETIME,
  Product CHAR(1)
)

INSERT #Test
SELECT 1,857,'2011-07-16 17:22:31.000','A' UNION ALL
SELECT 2,524,'2011-07-18 15:40:46.000','A' UNION ALL
SELECT 3,985,'2011-07-19 08:51:59.000','A' UNION ALL
SELECT 3,574,'2011-07-25 15:04:17.000','A' UNION ALL
SELECT 4,989,'2011-07-28 11:18:25.000','A' UNION ALL
SELECT 5,584,'2011-11-21 14:06:01.000','A' UNION ALL  
SELECT 6,547,'2011-11-22 11:51:54.000','A'

--select * from #Test

declare @date datetime
set @date='2011-07-18 15:40:46.000'
select * from #Test where Product='a'
and OrderDate between @date and dateadd(DD,1,@date)
--and OrderDate between getdate() and dateadd(d,1,getdate())

------解决方案--------------------
正在研究中

------解决方案--------------------
这个里面肯定要用datediff(hour,time1,time2)的

------解决方案--------------------
SQL code
select a.*
from #Test a,#Test b where a.product=b.product and a.CustID<>b.CustID
and ((datediff(minute,a.OrderDate,b.OrderDate)/60.0<=24 and datediff(minute,a.OrderDate,b.OrderDate)/60.0>=0)
or(datediff(minute,b