如何Mysql查詢這一年第X周的數據？

從周一到周日算的，這個時間區間應該怎麼寫？

用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來計算哦！

結果是：

你可以到這裡查看腳本：

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

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測試的。

更新的代碼如下：

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.