用MySQL的week函数。比如 用下面的测试数据:
CREATE TABLE Employees (
Id INT NOT NULL AUTO_INCREMENT,
FName VARCHAR(35) NOT NULL,
LName VARCHAR(35) NOT NULL,
PhoneNumber VARCHAR(11),
ManagerId INT,
DepartmentId INT NOT NULL,
Salary INT NOT NULL,
HireDate DATETIME NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO Employees
(Id, FName, LName, PhoneNumber, ManagerId, DepartmentId, Salary, HireDate)
VALUES
(1, 'James', 'Smith', 1234567890, NULL, 1, 1000, str_to_date('01-01-2002', '%d-%m-%Y')),
(2, 'John', 'Johnson', 2468101214, '1', 1, 400, str_to_date('08-01-2002', '%d-%m-%Y')),
(3, 'Michael', 'Williams', 1357911131, '1', 2, 600, str_to_date('30-12-2002', '%d-%m-%Y')),
(4, 'Johnathon', 'Smith', 1212121212, '2', 1, 500, str_to_date('31-12-2002', '%d-%m-%Y'))
我们可以用week得到每个日期在这一年里是第几周的(注意这个第几周是从0算起哦) :
SELECT e.HireDate, week(HireDate) FROM Employees e;
所以你可以利用这个函数来查找第几周的数据,比如我想查找这一年中第53周的数据,可以这样:
SELECT e.* FROM Employees e WHERE week(HireDate) = 53-1;
注意你要查53周,你得减一用52来计算哦!
结果是:
你可以到这里查看脚本:
Update:
有朋友通过comments反应说上面的写法在数据量大的情况下性能会有问题,确实是这样的。因为我们在上面有用到week(HireDate)这种写法,HireDate是一个column的名字,我们在column上应用了函数week,数据库会对Employees里面的每一个record都这样子计算一下,如果数据量大的话速度会非常非常慢。 有一个解决的思路是我们自己生成一个时间段内(根据我们自己的需要,比如我的数据是从2014年到现在的,我就可以对2014年到2020年的每一天生成一个日期)的日期,然后提前计算好每一周是从哪天到哪天,这样我们在查询的时候就可以借助这个数据集合来帮助我们查询。这样做为什么速度会快?因为数据量少呀,比如我的Employeestable里面可能只有两年的数据,但是可能会有十亿条!!!但是两年的时间段总共才不到800天,我们提前计算只要计算不到800次就可以。下面通过例子来演示一下。
先生成一堆数:
select t1.x * 100 + t2.x * 10 + t3.x
from
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t1,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t2,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t3
order by 1
结果如下:
这是从0到999共1000个数。然后稍作修改让它变成一堆日期,注意按你自己的日期范围修改哦:
select adddate('2002-01-01', t1.x * 100 + t2.x * 10 + t3.x) d
from
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t1,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t2,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t3
order by 1
这是1000个日期。然后在这些日期基础上分组计算一下:
select min(t.d) start_, max(t.d) end_, year(t.d) year_, week(t.d, 5) week_
from (
select adddate('2002-01-01', t1.x * 100 + t2.x * 10 + t3.x) d
from
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t1,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t2,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t3
) t
group by year(t.d), week(t.d, 5)
order by year_, week_
结果如下:
从这个结果我们就知道(看第一条数据!)第一周,也就是week_等于0的这一周,它是从2002年1月1日起到2002年1月6日,第二周是从...到...,等等等等。然后现在查询我们最上面的Employeestable的话可以这样子:
select *
from employees e
inner join (
select min(t.d) start_, max(t.d) end_
from (
select adddate('2002-01-01', t1.x * 100 + t2.x * 10 + t3.x) d
from
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t1,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t2,
(
select 0 as x union all
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9
) t3
) t
group by year(t.d), week(t.d, 5)
having min(t.d) = 2002 and min(week(t.d, 5)) = 52
) tmp
on e.HireDate >= tmp.start_ and e.HireDate < adddate(tmp.end_, 1)
结果如下,查询的是在第53周(注意SQL里写的是52,和上面一样哦)的数据:
having min(t.d) = 2002 and min(week(t.d, 5))这一部分写的有点啰嗦,因为不想再嵌套一层select专门就为了过滤一下数据,所以就想办法用having过滤了数据。这样修改以后应该会快很多,但是我没有测试环境,都是在web上用sqlfiddle测试的。
更新的代码如下: