数据
1 aa
2 aa
3 bb
4 bb
5 bb
6 aa
7 aa
8 aa
9 bb
10 bb要求只用SQL(几条不限,一条最好)选出第1、3、6、9项,就是每个不同分组的第一项,高手请赐教。
1 aa
2 aa
3 bb
4 bb
5 bb
6 aa
7 aa
8 aa
9 bb
10 bb要求只用SQL(几条不限,一条最好)选出第1、3、6、9项,就是每个不同分组的第一项,高手请赐教。
调试欢乐多
如不重复:select a.* from tt a
inner join
(select name,min(id) as mi from tt group by name) b
on a.name=b.name and a.id=b.mi
from tb a
where not exists (select 1 from tb b
where b.val=a.val
and b.id=a.id-1)
from 数据 as a inner join (select 分组, min(项) as minNo from 数据) as b
on a.项=b.minNo[align=center]==== 思想重于技巧 ====
[/align]
from 数据 as a , (select 分组, min(项) as minNo from 数据) as b
where a.项=b.minNo[align=center]==== 思想重于技巧 ====
[/align]
看一下,我就不给你写了/*Table structure for table `t` */DROP TABLE IF EXISTS `t`;CREATE TABLE `t` (
`id` int(11) NOT NULL auto_increment,
`g_id` int(11) NOT NULL,
`t_str` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
key (`g_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;/*Data for the table `t` */insert into `t`(`id`,`g_id`,`t_str`) values
(1,2,'wo'),
(2,2,'ni'),
(3,2,'ta'),
(4,3,'wo '),
(5,4,'ni'),
(6,3,'ni'),
(7,4,'ta'),
(8,3,'wang'),
(9,4,'li'),
(10,3,'hai'),
(11,4,'ri'),
(12,2,'ren'),
(13,5,'ta'),
(14,6,'ri'),
(15,6,'ren'),
(16,6,'fuck'),
(17,6,'shit'),
(18,5,'ls'),
(19,5,'chmod'),
(20,5,'chgrp'),
(21,5,'chown'),
(22,3,'rm'),
(23,3,'desc'),
(24,4,'pwd'),
(25,5,'cd');
1、相关子查询
(这个SQL语句是从ITPUB上来的。)
select a.* from t a where
(
select count(*) from t b where a.g_id = b.g_id and b.id<a.id
) < 2 order by a.g_id desc;
insert into tb values (1 , 'aa');
insert into tb values (2 , 'aa');
insert into tb values (3 , 'bb');
insert into tb values (4 , 'bb');
insert into tb values (5 , 'bb');
insert into tb values (6 , 'aa');
insert into tb values (7 , 'aa');
insert into tb values (8 , 'aa');
insert into tb values (9 , 'bb');
insert into tb values (10, 'bb');
select id, val from tb a where not exists (select 1 from tb b where b.val=a.val and b.id=a.id-1);
drop table tb;
+------+------+
| id | val |
+------+------+
| 1 | aa |
| 3 | bb |
| 6 | aa |
| 9 | bb |
+------+------+
SELECT a.* from tt2 a
left join tt2 b on a.name=b.name and a.id=b.id+1
where isnull(b.id)
用EXIST效率低了。
如果id不连续,用"exists"更容易实现逻辑变化:
create table tb (id int, val char(2));
insert into tb values (1 , 'aa');
insert into tb values (2 , 'aa');
insert into tb values (3 , 'bb');
insert into tb values (4 , 'bb');
insert into tb values (15 , 'bb');
insert into tb values (16 , 'aa');
insert into tb values (17 , 'aa');
insert into tb values (18 , 'aa');
insert into tb values (29 , 'bb');
insert into tb values (32 , 'bb'); select id, val
from tb a
where not exists (select 1 from tb b
where b.val=a.val
and b.id<a.id
and not exists (select 1 from tb c
where c.val!=a.val
and c.id>b.id
and c.id<a.id
)
);
drop table tb;+------+------+
| id | val |
+------+------+
| 1 | aa |
| 3 | bb |
| 16 | aa |
| 29 | bb |
+------+------+
[多说两句,不能白拿这么些分啊。 :p ]