# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# 主机: localhost
# 生成日期: 2010 年 12 月 03 日 21:44
# 服务器版本: 5.1.11
# PHP 版本: 5.2.6
#
# 数据库 : `test`
# # --------------------------------------------------------#
# 表的结构 `wsz1`
#CREATE TABLE `wsz1` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`money` varchar(20) NOT NULL,
`time` datetime NOT NULL,
`gid` int(11) NOT NULL,
UNIQUE KEY `id` (`id`),
KEY `time` (`time`),
KEY `gid` (`gid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz1`
#INSERT INTO `wsz1` (`id`, `name`, `money`, `time`, `gid`) VALUES (1, 'a', '10', '2010-10-01 00:00:00', 1),
(2, 'b', '20', '2010-10-01 00:00:00', 2),
(3, 'a', '11', '2010-11-01 00:00:00', 1),
(4, 'b', '12', '2010-11-01 00:00:00', 2),
(6, 'c', '11', '2010-12-01 00:00:00', 1),
(7, 'a', '9', '2010-12-01 00:00:00', 1);# --------------------------------------------------------#
# 表的结构 `wsz2`
#CREATE TABLE `wsz2` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`add` varchar(20) NOT NULL,
`tel` int(8) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz2`
#INSERT INTO `wsz2` (`id`, `name`, `add`, `tel`) VALUES (1, 'a', 'shanghai', 86666666),
(2, 'b', 'beijing', 688888888),
(3, 'c', 'beijing', 86868686);# --------------------------------------------------------#
# 表的结构 `wsz3`
#CREATE TABLE `wsz3` (
`gid` int(11) NOT NULL,
`gname` varchar(4) NOT NULL,
PRIMARY KEY (`gid`),
KEY `gname` (`gname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz3`
#INSERT INTO `wsz3` (`gid`, `gname`) VALUES (1, '组一'),
(2, '组二'),
(3, '组三');-------------------------------------------结果:实现成下结果:
name money time add tel group
c 11 2010-12-01 beijing 65432188 组一
a 9 2010-12-01 shanghai 85585856 组一
b 12 2010-11-01 beijing 88888888 组二
# version 2.5.6
# http://www.phpmyadmin.net
#
# 主机: localhost
# 生成日期: 2010 年 12 月 03 日 21:44
# 服务器版本: 5.1.11
# PHP 版本: 5.2.6
#
# 数据库 : `test`
# # --------------------------------------------------------#
# 表的结构 `wsz1`
#CREATE TABLE `wsz1` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`money` varchar(20) NOT NULL,
`time` datetime NOT NULL,
`gid` int(11) NOT NULL,
UNIQUE KEY `id` (`id`),
KEY `time` (`time`),
KEY `gid` (`gid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz1`
#INSERT INTO `wsz1` (`id`, `name`, `money`, `time`, `gid`) VALUES (1, 'a', '10', '2010-10-01 00:00:00', 1),
(2, 'b', '20', '2010-10-01 00:00:00', 2),
(3, 'a', '11', '2010-11-01 00:00:00', 1),
(4, 'b', '12', '2010-11-01 00:00:00', 2),
(6, 'c', '11', '2010-12-01 00:00:00', 1),
(7, 'a', '9', '2010-12-01 00:00:00', 1);# --------------------------------------------------------#
# 表的结构 `wsz2`
#CREATE TABLE `wsz2` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`add` varchar(20) NOT NULL,
`tel` int(8) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz2`
#INSERT INTO `wsz2` (`id`, `name`, `add`, `tel`) VALUES (1, 'a', 'shanghai', 86666666),
(2, 'b', 'beijing', 688888888),
(3, 'c', 'beijing', 86868686);# --------------------------------------------------------#
# 表的结构 `wsz3`
#CREATE TABLE `wsz3` (
`gid` int(11) NOT NULL,
`gname` varchar(4) NOT NULL,
PRIMARY KEY (`gid`),
KEY `gname` (`gname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz3`
#INSERT INTO `wsz3` (`gid`, `gname`) VALUES (1, '组一'),
(2, '组二'),
(3, '组三');-------------------------------------------结果:实现成下结果:
name money time add tel group
c 11 2010-12-01 beijing 65432188 组一
a 9 2010-12-01 shanghai 85585856 组一
b 12 2010-11-01 beijing 88888888 组二
+----+------+-------+---------------------+-----+
| id | name | money | time | gid |
+----+------+-------+---------------------+-----+
| 1 | a | 10 | 2010-10-01 00:00:00 | 1 |
| 2 | b | 20 | 2010-10-01 00:00:00 | 2 |
| 3 | a | 11 | 2010-11-01 00:00:00 | 1 |
| 4 | b | 12 | 2010-11-01 00:00:00 | 2 |
| 6 | c | 11 | 2010-12-01 00:00:00 | 1 |
| 7 | a | 9 | 2010-12-01 00:00:00 | 1 |
+----+------+-------+---------------------+-----+
6 rows in set (0.00 sec)mysql> select * from wsz2;
+----+------+----------+-----------+
| id | name | add | tel |
+----+------+----------+-----------+
| 1 | a | shanghai | 86666666 |
| 2 | b | beijing | 688888888 |
| 3 | c | beijing | 86868686 |
+----+------+----------+-----------+
3 rows in set (0.00 sec)mysql> select * from wsz3;
+-----+-------+
| gid | gname |
+-----+-------+
| 1 | 组一 |
| 2 | 组二 |
| 3 | 组三 |
+-----+-------+
3 rows in set (0.00 sec)mysql> select a.name,a.money,a.time,b.add,b.tel,c.gname as `group`
-> from wsz1 a inner join wsz2 b on a.name=b.name
-> inner join wsz3 c on a.gid=c.gid
-> where not exists (select 1 from wsz1 where name=a.name and time>a.time);
+------+-------+---------------------+----------+-----------+-------+
| name | money | time | add | tel | group |
+------+-------+---------------------+----------+-----------+-------+
| b | 12 | 2010-11-01 00:00:00 | beijing | 688888888 | 组二 |
| c | 11 | 2010-12-01 00:00:00 | beijing | 86868686 | 组一 |
| a | 9 | 2010-12-01 00:00:00 | shanghai | 86666666 | 组一 |
+------+-------+---------------------+----------+-----------+-------+
3 rows in set (0.03 sec)mysql>
-> from wsz1 a inner join wsz2 b on a.name=b.name
-> inner join wsz3 c on a.gid=c.gid
-> where not exists (select 1 from wsz1 where name=a.name and time>a.time) order by a.time desc ;