mysql如何取上周数据,如何在MySQL中查找上周的数据

I want to display data from

Q1: only last week of each student.

Q2: only last month of each student.

How can I achieve this?

CREATE TABLE `hw_homework` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`studentid` int(10) NOT NULL,

`subjectid` int(10) NOT NULL,

`assignment_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

`teacherid` int(10) NOT NULL,

`date` datetime NOT NULL,

PRIMARY KEY (`id`)

) ;

INSERT INTO `hw_homework` (`id`, `studentid`, `subjectid`, `assignment_name`, `teacherid`,

`date`) VALUES

(1, 29, 5, '5E', 20, '2012-11-04 13:58:40'),

(2, 15, 5, '32B', 20, '2012-11-04 13:59:54'),

(3, 29, 4, 'Q2A', 20, '2012-10-30 17:53:46'),

(4, 29, 11, '6E', 20, '2012-11-02 20:06:39'),

(5, 29, 11, 'C15', 20, '2012-10-16 20:06:30'),

(6, 15, 11, '7A', 20, '2012-09-19 20:08:05'),

(7, 29, 5, '3B', 20, '2012-09-14 20:08:12'),

(8, 29, 13, '6E', 32, '2012-10-29 20:23:46'),

(9, 29, 11, '7E', 18, '2012-10-30 14:35:14'),

(10, 2, 5, '5E', 20, '2012-10-21 13:58:40'),

(11, 2, 5, '5E', 20, '2012-10-30 13:58:40'),

(12, 2, 5, '5E', 20, '2012-10-31 13:58:40');

This does not work for last week. It shows this week result.

SELECT studentID,

DATE_FORMAT(`date`, '%U') `WeekNo`,

COUNT(studentID) totalMissed

FROM hw_homework he

WHERE DATE_FORMAT(`date`, '%U') = (SELECT MAX(DATE_FORMAT(NOW(), '%U')) FROM hw_homework hi WHERE hi.studentID = he.studentID)

-- AND studentID = ''

GROUP BY studentID, DATE_FORMAT(`date`, '%U')

This does not work for last month. This shows this month result.

SELECT studentID,

DATE_FORMAT(`date`, '%M') `Month`,

COUNT(studentID) totalMissed

FROM hw_homework he

WHERE DATE_FORMAT(`date`, '%M') = (SELECT MAX(DATE_FORMAT(NOW(), '%M')) FROM hw_homework hi WHERE hi.studentID = he.studentID)

-- AND studentID = ''

GROUP BY studentID, DATE_FORMAT(`date`, '%M')

Thanks in advance.

解决方案

try subtracting 1 from weekNo:

SELECT studentID,

DATE_FORMAT(`date`, '%U') `WeekNo`,

COUNT(studentID) totalMissed

FROM hw_homework he

WHERE DATE_FORMAT(`date`, '%U') =

(SELECT MAX(DATE_FORMAT(NOW(), '%U')-1)

FROM hw_homework hi

WHERE hi.studentID = he.studentID)

GROUP BY studentID, DATE_FORMAT(`date`, '%U')