请教怎样做内循环查询的问题
各位高人,表结构如下
[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
/*
22011-07-18 15:40:46.000524A
52011-11-21 14:06:01.000584A
[解决办法]
custid =6 这行数据没有参考的后续值，所以没有选出来.
[解决办法]
CREATE TABLE #Test ( CustID INT , OrderID INT , OrderDate DATETIME, Product CHAR(1))INSERT #TestSELECT 1,857,'2011-07-16 17:22:31.000','A' UNION ALLSELECT 2,524,'2011-07-18 15:40:46.000','A' UNION ALLSELECT 3,985,'2011-07-19 08:51:59.000','A' UNION ALLSELECT 3,574,'2011-07-25 15:04:17.000','A' UNION ALLSELECT 4,989,'2011-07-28 11:18:25.000','A' UNION ALLSELECT 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 #Testdeclare @date datetimeset @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)的
[解决办法]
select a.*from #Test a,#Test b where a.product=b.product and a.CustID<>b.CustIDand ((datediff(minute,a.OrderDate,b.OrderDate)/60.0<=24 and datediff(minute,a.OrderDate,b.OrderDate)/60.0>=0)or(datediff(minute,b.OrderDate,a.OrderDate)/60.0<=24 and datediff(minute,b.OrderDate,a.OrderDate)/60.0>=0))
[解决办法]
CREATE TABLE t1
(
CustID INT ,
OrderID INT ,
OrderDate DATETIME,
Product CHAR(1)
)
INSERT into t1
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 t1
;with aaa as
(
select ROW_NUMBER() over(order by custid) as id,* from t1
)
,bbb as
(
select a.CustID as CustID1,a.OrderID as OrderID1,a.OrderDate as OrderDate1,a.Product as Product1,
DATEDIFF(hour,a.OrderDate,b.OrderDate) as riqi,
b.CustID as CustID2,b.OrderID as OrderID2,b.OrderDate as OrderDate2,b.Product as Product2
from aaa as a inner join aaa as b on a.id=b.id-1
)
select CustID1 as CustID,OrderID1 as OrderID,OrderDate1 as OrderDate,Product1 as Product from bbb where riqi<24
union
select CustID2,OrderID2,OrderDate2,product2 from bbb where riqi<24
-----------------------------
CustIDOrderIDOrderDateProduct
25242011-07-18 15:40:46.000A
39852011-07-19 08:51:59.000A
55842011-11-21 14:06:01.000A
65472011-11-22 11:51:54.000A
