SQL操作小技巧:对同一个表操作

在同一张表里，可能同时进行insert和select的操作；进行delete和select的操作；或者update和select的操作。

现在以Mysql5.1为例。

表的定义：

CREATE TABLE `people` (
   `id` varchar(50) NOT NULL,
   `name` varchar(200) DEFAULT NULL,
   `nindex` int(9) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8

再往表写几天测试数据：

insert into `people` (`id`, `name`, `nindex`) values('1000','java','1');
insert into `people` (`id`, `name`, `nindex`) values('1001','csharp','2');
insert into `people` (`id`, `name`, `nindex`) values('1003','python','3');
insert into `people` (`id`, `name`, `nindex`) values('1005','javascript','3');

insert和select组合操作：

insert into people select max(id)+1,name,nindex from people limit 1

delete和select组合操作，这个时候也必须要用到表的别名：

delete from people where id in(select max(p.id) from (select id from people) as p)

update和select组合操作,这个时候要用到表的别名：

update people set nindex=(select max(p.nindex)+1 from (select nindex from people) as p) where id = '1001' 

分组查询
有时候在一个表保持几个分类，然后想取每个分类的前几条数据，SQL就是这样写的：

select id,part, name, sell from test a where 
(select count(1) from test b where b.part=a.part and b.id!=a.id
and b.name!=a.name and b.sell>a.sell)<2 order by part, sell desc;