請問這個需求該怎麼用SQL實現？

12-29

從第一張表中，找出每一條記錄的前7天之內有沒有同一個人的記錄，如果有的話在&<7days這一列輸出yes，沒有的話輸出no

最簡單的想法，分別inner join自己六次，條件為
on A.name = B.name and A.date = B.date-N天
N從1到6
最後你就會得到6張臨時表，告訴你某個(name, date)是否出現在前1天或前6天，只要有一個存在就no

堅持要用一個select的話會寫的很噁心（

-- 需要加 tb1.dt &> tb2.dt 的關聯條件，不然永遠會關聯同日期的記錄
-- 本質上是 0 &< tb1.dt - tb2.dt &< 7
Select tb1.Id, tb1.dt, tb1.name, tb1.other, case when min(tb2.id) is null then "no" else "yes" end as new_key
From table tb1
Left join table tb2
On tb1.id = tb2.id and tb1.dt &< tb2.dt + 7 and tb1.dt &> tb2.dt
Group by tb1.id, tb1.dt, tb1.name, tb1.other

不知道這叫什麼表，假設 test 吧

SELECT t.*, IF( EXISTS( SELECT * FROM test WHERE ADDDATE(test.date, 7) &>= t.date AND t.idnumber = test.idnumber ), "yes", "no") AS result FROM test t;

這個技術叫 Dependent-Subquery

--sql server SELECT a.*, CASE WHEN EXISTS(SELECT 1 FROM tb b WHERE a.idNumber = b.idNumber AND b.date &< a.date AND Datediff(DAY, b.date, a.date) &< 7) THEN "yes" ELSE "no" END "&<7days" FROM tb a

不限於標準SQL的話，可以用LAG函數，按人名分組，組內再按時間排序，計算與前一行的時間差， date - lag(date) over(partition by name order by date)

這函數在Oracle, SQL Server, DB2里都支持

好久沒寫SQL了，忘記是雙引號還是單引號，也不太確定IF的寫法。題主再調試下。
where的條件看起來稍顯繁瑣，是為了可以用到date欄位的索引，函數內的直接比較可能會導致沒法用到索引。這種查詢沒有利用到索引，性能會非常差。

mysql的話可以這麼寫：

select t3.name,t3.idnumber,t3.date,if(t4.name is null, "no", "yes") from test t3 left join ( select t1.name,t1.idnumber,t1.date from test t1 join test t2 on t1.name=t2.name where datediff(t1.date,t2.date)&>0 and datediff(t1.date,t2.date)&<7 ) t4 on t3.name=t4.name and t3.idnumber=t4.idnumber and t3.date=t4.date

select * into #temp from (
select "Len" name, "1" idnumber, cast("2016-7-1" as datetime) date, 84 other
union all
select "Rin" name, "2" idnumber, "2016-7-4" date, 43 other
union all
select "Miku" name, "3" idnumber, "2016-3-2" date, 71 other
union all
select "kaito" name, "4" idnumber, "2016-5-6" date, 66 other
union all
select "Len" name, "1" idnumber, "2016-7-3" date, 84 other
union all
select "Kaito" name, "4" idnumber, "2016-6-7" date, 80 other
union all
select "GUMI" name, "5" idnumber, "2016-9-1" date, 93 other
union all
select "IA" name, "6" idnumber, "2016-10-3" date, 65 other
union all
select "Kaito" name, "4" idnumber, "2016-6-9" date, 71 other
union all
select "IA" name, "6" idnumber, "2016-12-5" date, 51 other
union all
select "Rin" name, "2" idnumber, "2016-7-6" date, 22 other
union all
select "GUMI" name, "5" idnumber, "2016-5-3" date, 73 other
union all
select "Miku" name, "3" idnumber, "2016-6-5" date, 20 other
) T

select *, [&<7 Days] = (
case (select COUNT(0) from #temp T2 where date &>= dateadd(day, -7, T.date) and date &< T.date and name = T.name)
when 0 then "NO"
else "YES"
end
)
from #temp T
drop table #temp

作業要自己寫

在excel里只是一句話的事情，先用countif判斷某個時間段內某個名稱的出現次數，外面再加if函數，等於0的時候是no，否則yes。

同表left join on id=id and date between date = date +7
Where right table id is not null

根據日期做個ranking，group by id，然後看最近的是否在七日之內

我覺得直接在表裡加一列記錄這個，然後在添加記錄的時候直接維護會不會好些。。用數據冗餘換時間，反正不考慮訂單撤銷的話這一列的數據是不變的，要撤銷也可以另外維護。。

設表名a sql語法 n久沒寫了......

select * ,(case when (select count(* ) from a b where dateadd(dd,b.date,7)&0 then "yes"
else "no" end )
as other
from a

如果所有人的名字只出現兩次的話，你可以用case when 在inner join 是null的時候設定一個值（如1900年），這樣就可以一個CTE加一個Select就寫完了

按date+group+union不行嗎？

7日內留存么？

MySQL

SELECT a.*,(SELECT if(COUNT(*)&>0,"yes","no") FROM tableName WHERE name=a.name AND date &date_sub(a.date,interval 7 day)) `&<7days` FROM tableName a