56,675
社区成员
发帖
与我相关
我的任务
分享
mysql> SHOW CREATE TABLE QA\G;
*************************** 1. row ***************************
Table: QA
Create Table: CREATE TABLE `qa` (
`id` bigint(20) NOT NULL DEFAULT '0',
`name` varchar(2) NOT NULL DEFAULT '',
`loginTime` varchar(8) NOT NULL DEFAULT '',
`act` varchar(1) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SELECT * FROM QA;
+----+--------+-----------+-----+
| id | name | loginTime | act |
+----+--------+-----------+-----+
| 1 | 灏忔槑 | 2011-3-7 | A |
| 2 | 灏忔槑 | 2011-3-8 | C |
| 3 | 灏忔槑 | 2011-3-9 | A |
| 4 | 灏忛粍 | 2011-3-8 | A |
| 5 | 灏忛粍 | 2011-3-9 | C |
+----+--------+-----------+-----+
5 rows in set (0.00 sec)
mysql> SET NAMES GBK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM qa a WHERE NOT EXISTS(SELECT 1 FROM qa WHERE a.NAME=NAME AN
D a.loginTime<loginTime);
+----+------+-----------+-----+
| id | name | loginTime | act |
+----+------+-----------+-----+
| 3 | 小明 | 2011-3-9 | A |
| 5 | 小黄 | 2011-3-9 | C |
+----+------+-----------+-----+
2 rows in set (0.02 sec)
drop table if exists dms.A;
create table dms.A
select 1 id, '小明' name, '2011-3-7' loginTime, 'A' act union all
select 2 ,'小明','2011-3-8','C' union all
select 3 ,'小明','2011-3-9','A' union all
select 4,'小黄','2011-3-8','A' union all
select 5,'小黄','2011-3-9','C';
select a2.*
from(
select a.name, max(a.loginTime) md from dms.A a group by a.name
)a1, dms.A a2
where a1.name=a2.name
and a1.md=a2.loginTime;
-- 结果如下:
'3', '小明', '2011-3-9', 'A'
'5', '小黄', '2011-3-9', 'C'
drop table if exists dms.A;
create table dms.A
select 1 id, '小明' name, '2011-3-7' d, 'A' f union all
select 2 ,'小明','2011-3-8','C' union all
select 3 ,'小明','2011-3-9','A' union all
select 4,'小黄','2011-3-8','A' union all
select 5,'小黄','2011-3-9','C';
select a2.*
from(
select a.name, max(a.d) md from dms.A a group by a.name
)a1, dms.A a2
where a1.name=a2.name
and a1.md=a2.d;
-- 结果如下:
'3', '小明', '2011-3-9', 'A'
'5', '小黄', '2011-3-9', 'C'
select id,name,max(loginTime)
from a group by id,name;