mysql查询每月不重复的数据

mysql> select * from product group by concat(year(last_update),'-',week(last_update),day(last_update))  order by last_update;

或者

mysql> select * from product group by date_format(last_update,'%Y-%m-%d
') order by last_update;

+-----+-------------------------+---------------------+
| id  | product                 | last_update         |
+-----+-------------------------+---------------------+
| 154 | new Ipda-39-76          | 2012-09-28 10:46:46 |
| 155 | new Ipda-64-55-58       | 2012-09-29 10:46:46 |
| 156 | new Ipda-3-61-65        | 2012-09-30 10:46:46 |
| 157 | new Ipda-64-22-41-50    | 2012-10-01 10:46:46 |
| 159 | new Ipda-64-36-93-27    | 2012-10-02 10:46:46 |
| 160 | new Ipda-3-81-97-69     | 2012-10-03 10:46:46 |
| 161 | new Ipda-64-22-99-5-64  | 2012-10-04 10:46:46 |
| 122 | new Ipda-71             | 2012-10-05 10:46:46 |
| 123 | new Ipda-64-29          | 2012-10-06 10:46:46 |
| 124 | new Ipda-3-34           | 2012-10-07 10:46:46 |
|  59 | new Ipda-39             | 2012-10-08 10:46:46 |
|  60 | new Ipda-64-55          | 2012-10-09 10:46:46 |
|  61 | new Ipda-3-61           | 2012-10-10 10:46:46 |
|  62 | new Ipda-64-22-41       | 2012-10-11 10:46:46 |
|  64 | new Ipda-64-36-93       | 2012-10-12 10:46:46 |
|  65 | new Ipda-3-81-97        | 2012-10-13 10:46:46 |
|  66 | new Ipda-64-22-99-5     | 2012-10-14 10:46:46 |
|   1 | new Ipda                | 2012-10-15 10:46:46 |
|   2 | new Ipda-64             | 2012-10-16 10:46:46 |
|   3 | new Ipda-3              | 2012-10-17 10:46:46 |
|   4 | new Ipda-64-22          | 2012-10-18 10:46:46 |
|   7 | new Ipda-64-36          | 2012-10-19 10:46:46 |
|   8 | new Ipda-3-81           | 2012-10-20 10:46:46 |
|   9 | new Ipda-64-22-99       | 2012-10-21 10:46:46 |
|  15 | new Ipda-3-17           | 2012-10-22 10:46:46 |
|  16 | new Ipda-64-22-27       | 2012-10-23 10:46:46 |
|  18 | new Ipda-64-36-38       | 2012-10-24 10:46:46 |
|  19 | new Ipda-3-81-41        | 2012-10-25 10:46:46 |
|  20 | new Ipda-64-22-99-90    | 2012-10-26 10:46:46 |
|  34 | new Ipda-3-81-26        | 2012-10-27 10:46:46 |
|  35 | new Ipda-64-22-99-10    | 2012-10-28 10:46:46 |
|  38 | new Ipda-3-17-70        | 2012-10-29 10:46:46 |
|  39 | new Ipda-64-22-27-36    | 2012-10-30 10:46:46 |
|  41 | new Ipda-64-36-38-44    | 2012-10-31 10:46:46 |
|  42 | new Ipda-3-81-41-9      | 2012-11-01 10:46:46 |
|  43 | new Ipda-64-22-99-90-13 | 2012-11-02 10:46:46 |
+-----+-------------------------+---------------------+
36 rows in set

评论

2 楼 红小豆 2015-12-16  

红小豆 写道

这个应该是按月/天查找,每天/月一条数据
真正的每月重复的数据要看重复的条件是什么
比如:重复的条件是名字和数量相同
则可以通过下面的语句来查询
select date_format(date,'%Y-%m-%d'),a.*
from book
where name,qty in
(select book,qty
  from book
  group by name,qty,date_format(date,'%Y-%m')
  having count(*)=1
)
查询每月不重复的订单
嗯,叫我红领巾

select date_format(a.date,'%Y-%m-%d'),a.*
from t_order a
where (a.book_id,a.qty) in
(select book_id,qty
  from t_order
  group by book_id,qty,date_format(date,'%Y-%m')
  having count(*)=1
)

1 楼 红小豆 2015-12-16  

这个应该是按月/天查找,每天/月一条数据
真正的每月重复的数据要看重复的条件是什么
比如:重复的条件是名字和数量相同
则可以通过下面的语句来查询
select date_format(date,'%Y-%m-%d'),a.*
from book
where name,qty in
(select book,qty
  from book
  group by name,qty,date_format(date,'%Y-%m')
  having count(*)=1
)
查询每月不重复的订单
嗯,叫我红领巾