sql 统计

Question

CREATE TABLE list
(
uid INT,
date DATETIME,
[money] DECIMAL(18,2)
)
INSERT INTO list
SELECT 1000,'2010-01-01',100
UNION ALL SELECT 1000,'2010-02-01',230
UNION ALL SELECT 1000,'2010-03-01',444
UNION ALL SELECT 1000,'2010-04-01',78
UNION ALL SELECT 1002,'2010-05-01',789
UNION ALL SELECT 1001,'2010-06-01',87
UNION ALL SELECT 1001,'2010-07-01',74
UNION ALL SELECT 1001,'2010-09-01',144
UNION ALL SELECT 1000,'2010-09-01',1000
SELECT * FROM list
找出连续三个月有消费的uid的记录

Answer 1

SELECT uid 
FROM list t
WHERE EXISTS(
          SELECT * 
          FROM list
          WHERE uid = t.uid
           AND ABS(DATEDIFF(mm, date, t.date)) = 1
      )
GROUP BY uid
HAVING COUNT(1)>=3
/*
uid
-----------
1000(1 行受影响)
*/

Answer 2

SELECT * 
FROM list l
WHERE EXISTS(
          SELECT 1 
          FROM list t
          WHERE EXISTS(
                    SELECT 1 
                    FROM list
                    WHERE uid = t.uid
                     AND ABS(DATEDIFF(mm, date, t.date)) = 1
                )
          GROUP BY uid
          HAVING COUNT(1)>=3
          AND uid=l.uid
      )
/*
uid         date                    money
----------- ----------------------- ---------------------------------------
1000        2010-01-01 00:00:00.000 100.00
1000        2010-02-01 00:00:00.000 230.00
1000        2010-03-01 00:00:00.000 444.00
1000        2010-04-01 00:00:00.000 78.00
1000        2010-09-01 00:00:00.000 1000.00(5 行受影响)
*/

Answer 3

;with t
as
(
select uid, DATEPART(MONTH,date)%3 mon ,date,[money]
from list
)
select distinct a.*
from list a join (select * from t where mon=0)b on a.uid=b.uid
     join (select * from t where mon=1) c on a.uid=c.uid
     join (select * from t where mon=2) d on a.uid=d.uiduid date money
1000 2010-01-01 00:00:00.000 100.00
1000 2010-02-01 00:00:00.000 230.00
1000 2010-03-01 00:00:00.000 444.00
1000 2010-04-01 00:00:00.000 78.00
1000 2010-09-01 00:00:00.000 1000.00

Answer 4

;WITH CTE
AS
(
select uid,
[Month]=month(date),
OrderNum=DENSE_RANK() 
over(partition by uid,year(date) order by month(date))
from list
)SELECT UID
FROM CTE A
WHERE EXISTS
(
SELECT 1
FROM CTE B
WHERE B.UID=A.UID
and B.[Month]-A.[Month]=3
and B.OrderNum-A.OrderNum=3
)UID
-----------
1000(1 行受影响)