RT,我表的结果如下
id uid pid date serverid
1 111 000 2011-08-18 3
2 112 001 2011-08-18 3
3 113 002 2011-08-18 3
...需求是查询上上周(2周前)的所有pid
和查询上上月(6月)的所有的pid请问该如何写?
id uid pid date serverid
1 111 000 2011-08-18 3
2 112 001 2011-08-18 3
3 113 002 2011-08-18 3
...需求是查询上上周(2周前)的所有pid
和查询上上月(6月)的所有的pid请问该如何写?
select pid from tbname where month(`date`)=6 and year(`date`)=2011;
+-------------------------------------------------------------------------------------+
| DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE())-1 DAY), INTERVAL 2 WEEK) |
+-------------------------------------------------------------------------------------+
| 2011-07-31 |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
select pid,count(pid) as count from active_log where year(date)=year(date_add("'.$thisdate.'",interval -1 month)) and month(date)=month(date_add("'.$thisdate.'",interval -1 month))
可行?
FROM table a
(
SELECT x.start, x.start + INTERVAL 6 DAY AS end
FROM
(SELECT (CURDATE() - INTERVAL 2 WEEK - INTERVAL DAYOFWEEK(CURDATE()) - 1 DAY) AS start) x
) b
WHERE a.date BETWEEN x.start AND x.end
SELECT pid
FROM table a
(
SELECT x.start, x.start + INTERVAL 1 MONTH - INTERVAL 1 DAY AS end
FROM
(SELECT (CURDATE() - INTERVAL 2 MONTH - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) AS start) x
) b
WHERE a.date BETWEEN x.start AND x.end