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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 3011 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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

第 1 条附言 · 2016-08-26 14:51:30 +08:00

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

SQL

QAQ

求教

表里

24 条回复 • 2016-10-13 23:35:36 +08:00

KagamineLenKai2

2016-08-26 11:23:31 +08:00

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

xujif

2016-08-26 11:31:17 +08:00

写可以写出来，不过估计效率堪忧。 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

2016-08-26 11:45:45 +08:00

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

cont

2016-08-26 12:00:50 +08:00

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

bugsnail

2016-08-26 12:01:25 +08:00

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

KagamineLenKai2

2016-08-26 12:02:27 +08:00

@cont
@bugsnail
谢谢！

cont

2016-08-26 12:05:33 +08:00

@bugsnail 原来是这样，学习了

luckylion

2016-08-26 12:41:10 +08:00

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

2016-08-26 12:44:18 +08:00

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

ebony0319

2016-08-26 12:44:22 +08:00

我觉得用内联自己连自己。然后比较日期比较。假设表是 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

2016-08-26 14:04:34 +08:00

这个，很容易啊，假设表是 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

2016-08-26 14:05:28 +08:00

这个，很容易啊，假设表是 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

2016-08-26 14:16:54 +08:00

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

2016-08-26 15:03:50 +08:00

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

luckylion

2016-08-26 15:11:37 +08:00

刚才看错了
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

2016-08-26 15:32:46 +08:00

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](