CREATE TABLE `t1` (
`id` int(11) NOT NULL auto_increment,
`cdate` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;--
-- 导出表中的数据 `t1`
-- INSERT INTO `t1` VALUES (1, '2011-05-14');
INSERT INTO `t1` VALUES (2, '2011-04-17');-- ----------------------------------------------------------
-- 表的结构 `t2`
-- CREATE TABLE `t2` (
`id` int(11) NOT NULL auto_increment,
`tid` int(11) NOT NULL,
`tdate` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;--
-- 导出表中的数据 `t2`
-- INSERT INTO `t2` VALUES (1, 2, '2011-04-24');
INSERT INTO `t2` VALUES (2, 2, '2011-05-14');
INSERT INTO `t2` VALUES (3, 2, '2011-05-26');
INSERT INTO `t2` VALUES (4, 2, '2011-06-20');
以上是数据库信息,其中表t2中的tid就是表t1中的id。
我要的查询语句是:表t1中的数据,如果对应的表t2中也有数据,那么就要表t2中最新的那条。
例如:我现在要的信息是1, '2011-05-14' NULL ....2, '2011-04-17' 4, 2, '2011-06-20'谢谢大家了
cast(tdate as date)假如表2还有其他字段,类型不定的话,有办法么,
举例说明
select * from t2 a where not exists(select 1 t2 where a.tid=tid and a.tdate<tdate)
(select tid,max(tdate) as ma from t2 group by tid) b on a.tid=b.tid and a.tdate=b.ma
[征集]分组取最大N条记录方法征集,及散分....
+----+------------+
| id | cdate |
+----+------------+
| 1 | 2011-05-14 |
| 2 | 2011-04-17 |
+----+------------+
2 rows in set (0.00 sec)mysql> select * from t2;
+----+-----+------------+
| id | tid | tdate |
+----+-----+------------+
| 1 | 2 | 2011-04-24 |
| 2 | 2 | 2011-05-14 |
| 3 | 2 | 2011-05-26 |
| 4 | 2 | 2011-06-20 |
+----+-----+------------+
4 rows in set (0.00 sec)mysql> select *
-> from t1 left join t2 on t1.id=t2.tid
-> group by t1.id;
+----+------------+------+------+------------+
| id | cdate | id | tid | tdate |
+----+------------+------+------+------------+
| 1 | 2011-05-14 | NULL | NULL | NULL |
| 2 | 2011-04-17 | 1 | 2 | 2011-04-24 |
+----+------------+------+------+------------+
2 rows in set (0.02 sec)mysql>
select * from t1 left join (select tid,max(tdate) from t2 group by tid) t3 on t1.id=t3.tid