这条SQL语句应该怎么写?
mysql> select * from a;
+----+-----+---------------------+
| id | bid | time |
+----+-----+---------------------+
| 1 | 1 | 2007-08-10 01:40:31 |
| 2 | 1 | 2007-08-15 01:34:11 |
| 3 | 3 | 2007-08-10 01:49:27 |
| 4 | 3 | 2007-08-10 01:49:27 |
+----+-----+---------------------+
mysql> select * from b;
+----+--------+
| id | name |
+----+--------+
| 1 | willko |
| 2 | willko |
| 3 | willko |
+----+--------+
a.bid外联b.id,一(b)对多(a)
现在要统计出b有多少条记录最近2天没有在b中插入新记录
这里表b id为1种有新记录,所以不算
而id为2在表a中没有记录,所以算一条
id为3在表中的记录是5天前的,所以算一条
结果为2条。
mysql是4的
谢谢了~~第一次写这种SQL~~自己也在看书··
sql~~
CREATE TABLE `a` (
`id` int(11) NOT NULL auto_increment,
`bid` int(11) NOT NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `bid` (`bid`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 AUTO_INCREMENT=5 ;
INSERT INTO `a` (`id`, `bid`, `time`) VALUES (1, 1, '2007-08-10 01:40:31'),
(2, 1, '2007-08-15 01:34:11'),
(3, 3, '2007-08-10 01:49:27'),
(4, 3, '2007-08-10 01:49:27');
CREATE TABLE `b` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 AUTO_INCREMENT=4 ;
INSERT INTO `b` (`id`, `name`) VALUES (1, 'willko'),
(2, 'willko'),
(3, 'willko');