求教，这段 SQL 该怎么写……

[KagamineLenKai2]

从上图这张表里，找到每一条记录的前七天之内有没有同一人的记录

然后生成这张表，请问 SQL 该怎么写 QAQ

额，直接输出成这个样子就好……但我还是找不到方法TwT

[KagamineLenKai2]

本菜鸡才意识到 V2EX 的发帖器是 Markdown 格式的，为了发个帖还现去百度的 Markdown 语法 /(ㄒoㄒ)/~~

[xujif]

写可以写出来，不过估计效率堪忧。 select * from table as t1 where not exists (select 1 from table as t2 where t1.name=t2.name and t2.date > date_sub(t1.date,interval 7 day) and t2.date < t1.date);

[KagamineLenKai2]

@xujif 啊，谢谢，不过第一个括号里的(select 1 ……这里有点没看懂？ 1 是？

[cont]

@KagamineLenKai2 1 是代表第一个字段，就是 select name

[bugsnail]

@KagamineLenKai2 那个是 1 表示 true,配合外面那个 exists

[KagamineLenKai2]

@cont
@bugsnail
谢谢！

[cont]

@bugsnail 原来是这样，学习了

[luckylion]

SELECT COUNT(*) AS total,t.*
FROM table AS t
WHERE DATE_SUB(CURDATE(), INTERVAL 14 DAY) <= DATE(`data`) AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) >= DATE(`data`)
GROUP BY idnumber
having total > 1

[luckylion]

where date_sub(curdate(), INTERVAL 7 DAY) <= date(`data`)

[ebony0319]

我觉得用内联自己连自己。然后比较日期比较。假设表是 tt
select t1.name,t1.idnumber,t1.date,t1.other, case when datediff(wk,t2.date,t1.date)>7 then 'yes' end AS '<7day' from tt AS t1 inner join tt AS t2 on t1.idnumber=t2.idnumber
where datediff(wk,t2.date,t1.date)>7

[liyanggyang]

这个，很容易啊，假设表是 table.
select name,idnumber,date,other,(case when counts>0 then 'yes' else 'no' end) ) '<7day' from (
select name,idnumber,date,other,
(select count(1) from table where idnumber=t.idnumber and name=t.name
and trunc(date) between trunc(t.date-7) and t.date ) counts
from table t
);

[liyanggyang]

这个，很容易啊，假设表是 table.
select name,idnumber,date,other,(case when counts>0 then 'yes' else 'no' end) ) "<7day" from (
select name,idnumber,date,other,
(select count(1) from table where idnumber=t.idnumber and name=t.name
and trunc(date) between trunc(t.date-7) and t.date ) counts
from table t
);

[liyanggyang]

select name,idnumber,date,other,(case when counts>0 then 'yes' else 'no' end) "<7day" from (
select name,idnumber,date,other,
(select count(1) from table where idnumber=t.idnumber and name=t.name
and date between trunc(t.date-7) and trunc(t.date)) counts
from table t
);

[luckylion]

SELECT t.*,if(COUNT(*)>1,"yes","no") AS `<7days`
FROM table AS t
where 时间判断自己写
GROUP BY idnumber

[luckylion]

刚才看错了
SELECT *
FROM table t
LEFT JOIN
(
SELECT idnumber, IF(COUNT(*)>1,"yes","no") AS `<7days`
FROM table
WHERE 时间判断
GROUP BY idnumber
) AS t2 ON t.idnumber=t2.idnumber

[luckylion]

SELECT *, IF((
SELECT 1
FROM TABLE
WHERE id < t.`id` AND idnumber = t.`idnumber`
ORDER BY id
LIMIT 1),"yes","no") AS `<7days`
FROM TABLE t
where 时间段

[Imgur]( )
最好能有个自动字段

[KagamineLenKai2]

@luckylion 请问一下第二个 SELECT 后面的 1 是什么意思？

[luckylion]

就是一个常量,有信息时直接用这个常量代替.速度要比显示出表里的信息速度快些
select 1 from 可以
select 2 from 也可以
select 'aa' from 还可以 O(∩_∩)O

[KagamineLenKai2]

@luckylion 这样(⊙o⊙)

[Layne]

@KagamineLenKai2 因为这里只是要判断是否 exists ，而不关心 exists 的记录的具体字段，但是 sql 语法不允许 select 后面没有 内容，那就用个常量来 填充咯

[zjgsamuel]

LZ 有没有考虑以下除了把 SQL 写出来后 结合使用场景 同设计数据库的提些出于查询效率上的意见呢?

[qq316107934]

做 V 家音乐库？

[javaluo]

select case when count(select idnumber from x where date - interval 7 days)>0

不过效率很低啊 数据分析还好，线上就不要这么干了

[15115199878]

@luckylion 很好，