【已解决】一条查询的写法

闸北陆小洪

#add by cenalulu：解决方案见2楼

加入有表如下：

user_id create_time
1 2011-11-23 20:17:09
1 2011-11-24 10:44:04
1 2012-05-31 19:32:04
1 2011-12-19 10:50:37
1 2011-11-26 13:11:55
1 2012-07-05 15:31:47
2 2012-05-30 19:32:04
2 2012-07-05 16:32:04
3 2012-07-05 23:32:04
4 2012-07-08 23:32:04

如何能查出create_time是2012-07-05的  并且这个user_id只有一条记录的

上表应该只查出3

cenalulu

select * from
(
select user_id
from
table1
group by user_id
having count(*) =1
) t
join
table1 a on t.user_id = a.user_id and a.create_time between  '2012-07-05' and '2012-07-05 23:59:59'

ajdan4q

回复 1# 闸北陆小洪


    试试

1. select * from tb
   
2. where create_time between '2012-07-05 00:00:00' and '2012-07-05 23:59:59'
   
3. group by id
   
4. having count(id) =1

复制代码

cenalulu

回复 3# ajdan4q


    这样的话1，2，3都会被选出来哦，亲

ajdan4q

回复 4# cenalulu


    步骤有点搞错了，谢谢提醒。

1. select table1.* from
   
2. (
   
3. select user_id
   
4. from
   
5. table1
   
6. group by user_id
   
7. having count(*) =1
   
8. ) t
   
9. join
   
10. table1 a on t.user_id = a.user_id and a.create_time between  '2012-07-05' and '2012-07-05 23:59:59'

复制代码

bikong0411

赞@cenalulu

闸北陆小洪

回复 2# cenalulu


thx

   

seesea2517

1. mysql> select * from user_test;
   
2. +---------+---------------------+
   
3. | user_id | create_time         |
   
4. +---------+---------------------+
   
5. |       1 | 2011-11-23 20:17:09 |
   
6. |       1 | 2011-11-24 10:44:04 |
   
7. |       1 | 2012-05-31 19:32:04 |
   
8. |       1 | 2011-12-19 10:50:37 |
   
9. |       1 | 2011-11-26 13:11:55 |
   
10. |       1 | 2012-07-05 15:31:47 |
    
11. |       2 | 2012-05-30 19:32:04 |
    
12. |       2 | 2012-07-05 16:32:04 |
    
13. |       3 | 2012-07-05 23:32:04 |
    
14. |       4 | 2012-07-08 23:32:04 |
    
15. +---------+---------------------+
    
16. 10 rows in set (0.00 sec)
    
17. 
    
18. mysql> select * from user_test group by user_id having create_time like "%2012-0
    
19. 7-05%" and count(user_id) = 1;
    
20. +---------+---------------------+
    
21. | user_id | create_time         |
    
22. +---------+---------------------+
    
23. |       3 | 2012-07-05 23:32:04 |
    
24. +---------+---------------------+
    
25. 1 row in set (0.01 sec)
    
26. 
    

复制代码