T-SQL Select在一段时间内返回结果

Question

我试过了，但我不能弄明白这一点。我有一个transactions表(transaction_ID、transaction_Person_ID、Transaction_Date等)。我想要的是返回所有transaction_person_ID，它们在过去一年中每周有超过3个事务。这意味着我必须检查1-1-10到7-10-10，看看是否有人在那周有超过3次交易，然后是2-1-10到8-10-10等等。

WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATETIME,'01/01/2010')
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < '12/31/2010'
)

SELECT transaction_person_Id FROM transactions
JOIN DATES
ON transactions.transaction_date = dates.date
where transactions.Transaction_Date between dateadd(DAYOFYEAR,-7,dates.date) and dates.date
group by transaction_person_Id
having count(transaction_person_ID) >= 4
OPTION (MAXRECURSION 2000)

谢谢

Answer 1

我现在还没有ms-sql的实例，所以我不能测试它，但是一旦不可避免的语法错误被纠正，它应该可以做你想要的事情

select 
    transaction_person_ID
from
(
    select 
        transaction_person_Id, 
        count(transaction_person_id), 
        datepart(wk,Transaction_date)
    from
        transactions
    Where
        Transaction_date > dateadd(d,-datepart(dy,getdate())+1,getdate())
    group by 
        transaction_person_Id, 
        datepart(wk,Transaction_date)
    having
        count(transaction_person_id) >3
) as foo
group by
   transaction_person_id
having
   count(transaction_person_id) >= datepart(wk,getdate)

希望这能有所帮助！