版主大大,好像不对哦,这是我用你的方式查询出来的结果: mysql> select * from tj_user_t a where not exists(select 1 from tj_user_t a where a.udid=udid and a.active_date<active_date) and a.active_date='2013-12-24'; +----+------+-------------+-------------+-----+ | id | udid | active_date | active_time | num | +----+------+-------------+-------------+-----+ | 3 | 王五 | 2013-12-24 | 12:03:05 | 0 | | 4 | 张三 | 2013-12-24 | 02:20:09 | 0 | | 6 | 李四 | 2013-12-24 | 12:26:26 | 0 | +----+------+-------------+-------------+-----+ 3 rows in set
select * from tt a where not exists(select 1 from tt a where a.udid=udid and a.active_date>active_date) and a.active_date='2013-12-24' or select * from tt a where not exists(select 1 from tt a where a.udid=udid and a.active_date<>active_date) and a.active_date='2013-12-24'
mysql> select * from tj_user_t a where not exists(select 1 from tj_user_t a where a.udid=udid and a.active_date>active_date) and a.active_date='2013-12-24'; +----+------+-------------+-------------+-----+ | id | udid | active_date | active_time | num | +----+------+-------------+-------------+-----+ | 3 | 王五 | 2013-12-24 | 12:03:05 | 0 | | 4 | 张三 | 2013-12-24 | 02:20:09 | 0 | | 5 | 赵六 | 2013-12-24 | 04:26:08 | 0 | | 6 | 李四 | 2013-12-24 | 12:26:26 | 0 | +----+------+-------------+-------------+-----+ 4 rows in setmysql> select * from tj_user_t a where not exists(select 1 from tj_user_t a where a.udid=udid and a.active_date<>active_date) and a.active_date='2013-12-24'; +----+------+-------------+-------------+-----+ | id | udid | active_date | active_time | num | +----+------+-------------+-------------+-----+ | 3 | 王五 | 2013-12-24 | 12:03:05 | 0 | | 4 | 张三 | 2013-12-24 | 02:20:09 | 0 | | 5 | 赵六 | 2013-12-24 | 04:26:08 | 0 | | 6 | 李四 | 2013-12-24 | 12:26:26 | 0 | +----+------+-------------+-------------+-----+ 4 rows in set结果还是不对, 下面是创建表:CREATE TABLE `tj_user_t` ( `id` int(11) NOT NULL auto_increment, `udid` varchar(255) NOT NULL COMMENT '设备唯一标识', `active_date` date default NULL COMMENT '调用日期,格式:yyyy-mm-dd (使用CURDATE())', `active_time` time default NULL COMMENT '调用的时间,格式:hh:mm:ss (使用CURTIME())', `num` int(10) unsigned zerofill NOT NULL, PRIMARY KEY (`id`,`udid`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gb2312;
谢谢版主,已经搞定了。SELECT * from tj_user_t WHERE active_date != '2013-12-24' and active_date NOT IN (SELECT udid FROM tj_user_t tt WHERE tt.active_date != '2013-12-24');
版主大大,好像不对哦,这是我用你的方式查询出来的结果:
mysql> select * from tj_user_t a where not exists(select 1 from tj_user_t a where a.udid=udid
and a.active_date<active_date) and a.active_date='2013-12-24';
+----+------+-------------+-------------+-----+
| id | udid | active_date | active_time | num |
+----+------+-------------+-------------+-----+
| 3 | 王五 | 2013-12-24 | 12:03:05 | 0 |
| 4 | 张三 | 2013-12-24 | 02:20:09 | 0 |
| 6 | 李四 | 2013-12-24 | 12:26:26 | 0 |
+----+------+-------------+-------------+-----+
3 rows in set
and a.active_date>active_date) and a.active_date='2013-12-24'
or
select * from tt a where not exists(select 1 from tt a where a.udid=udid
and a.active_date<>active_date) and a.active_date='2013-12-24'
and a.active_date>active_date) and a.active_date='2013-12-24';
+----+------+-------------+-------------+-----+
| id | udid | active_date | active_time | num |
+----+------+-------------+-------------+-----+
| 3 | 王五 | 2013-12-24 | 12:03:05 | 0 |
| 4 | 张三 | 2013-12-24 | 02:20:09 | 0 |
| 5 | 赵六 | 2013-12-24 | 04:26:08 | 0 |
| 6 | 李四 | 2013-12-24 | 12:26:26 | 0 |
+----+------+-------------+-------------+-----+
4 rows in setmysql> select * from tj_user_t a where not exists(select 1 from tj_user_t a where a.udid=udid
and a.active_date<>active_date) and a.active_date='2013-12-24';
+----+------+-------------+-------------+-----+
| id | udid | active_date | active_time | num |
+----+------+-------------+-------------+-----+
| 3 | 王五 | 2013-12-24 | 12:03:05 | 0 |
| 4 | 张三 | 2013-12-24 | 02:20:09 | 0 |
| 5 | 赵六 | 2013-12-24 | 04:26:08 | 0 |
| 6 | 李四 | 2013-12-24 | 12:26:26 | 0 |
+----+------+-------------+-------------+-----+
4 rows in set结果还是不对, 下面是创建表:CREATE TABLE `tj_user_t` (
`id` int(11) NOT NULL auto_increment,
`udid` varchar(255) NOT NULL COMMENT '设备唯一标识',
`active_date` date default NULL COMMENT '调用日期,格式:yyyy-mm-dd (使用CURDATE())',
`active_time` time default NULL COMMENT '调用的时间,格式:hh:mm:ss (使用CURTIME())',
`num` int(10) unsigned zerofill NOT NULL,
PRIMARY KEY (`id`,`udid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gb2312;
谢谢版主,已经搞定了。SELECT * from tj_user_t WHERE active_date != '2013-12-24' and active_date NOT IN (SELECT udid FROM tj_user_t tt WHERE tt.active_date != '2013-12-24');