1.Select all people from [summary] table, purchase #12 product, the time interval (1st time purchase and 2nd time purchase ) less than 87 days. 筛选出购买12号产品,第一次和第二次购买的时间相差小于87天的人。
SELECT A.id FROM
(SELECT id,type,sequence,date,scr,updated_date FROM summary WHERE type=12 AND sequence='D1') A,
(SELECT id,type,sequence,date,scr,updated_date FROM summary WHERE type=12 AND sequence='D2') B
WHERE A.id=B.id
AND DATEDIFF(day,A.date,B.date) BETWEEN 1 AND 86
AND A.scr IS NOT NULL
AND B.scr IS NOT NULL
ORDER BY B.updated_date DESC
2.Base on the 1st senario, select all people who have addtional remarks for their purchase 2 times less than 87 days. 在第1条基础上,查看是否有人给了特殊说明。
SELECT TOP 10 A.id FROM
(SELECT id,type,sequence,date,scr,updated_date FROM summary WHERE type=12 AND sequence='D1') A,
(SELECT S.id,S.type,S.sequence,S.date,S.scr,S.updated_date
FROM summary