mysql删除重复字段

FROM子句中的子查询

2012年2月16日

14:29

1)查询语句中的的from字句的子查询

SELECT ... FROM (subquery) [AS] name ...

[AS] name子句是强制性的，因为FROM子句中的每个表必须有一个名称。在子查询选择列表中的任何列都必须有唯一的名称。您可以在myxql手册中的其它地方找到对本语法的说明。在该处，所用的词语是“导出表”。

为了进行详细说明，假设您有如下一个表：

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

下面使用了示例表，解释了在FROM子句中如何使用子查询：

INSERT INTO t1 VALUES (1,'1',1.0);

INSERT INTO t1 VALUES (2,'2',2.0);

SELECT sb1,sb2,sb3

FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1)AS sb

WHERE sb1 > 1;

结果：2, '2',4.0。

下面是另一个例子：假设您想了解一个分类后的表的一组和的平均值。采用如下操作：

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

不过，本查询提供所需的信息：

SELECT AVG(sum_column1)

FROM (SELECT SUM(column1) AS sum_column1

FROM t1 GROUP BY column1) AS t1;

注意，在子查询中使用的列名称（sum_column1）被整理到外部查询中。

FROM子句中的子查询可以返回标量、列、行或表。FROM子句中的子查询不能为有关联的子查询。

即使对EXPLAIN语句（即建立临时导出表），FROM子句中的子查询也会被执行。这是因为在优化过程中，上一级的查询需要有关所有表的信息。

2)更新语句{Delete|Update|Insert|Replace}语句中的from字句的子查询

比如一张存储用户拥有车型的表

create table mycar (id int(1) auto_increment, uservarchar(10) not null , carid int(10), status tinyint(1),primary key (`id`) )engine=myisam;

insert into mycar values('houweizong',1,1);

insert into mycar values('houweizong',2,1);

insert into mycar values('houweizong',3,1);

insert into mycar values('sunlintao',4,1);

insert into mycar values('sunlintao',5,1);

insert into mycar values('sunlintao',6,1);

insert into mycar values('xiaoer',7,1);

insert into mycar values('xiaosan',7,1);

mysql> select count(*) as num,user from mycar groupby user having num>0 order by num desc;

+-----+------------+

| num | user      |

+-----+------------+

|   3 |houweizong |

|   3 |sunlintao  |

|   1 |xiaoer     |

|   1 |xiaosan    |

+-----+------------+

4 rows in set (0.00 sec)

查询得知前两用户的记录有重复记录，但系统要求每个人只能有一条记录，现在需求为删除重复记录，每个用户只留一条记录

mysql> Update mycar as u1,mycar as u2 set u1.status=0 where  u1.id not in (select min(id) as id from mycaras u3 where u3.status=1 group by u3.user);

ERROR 1146 (42S02): Table 'study.u2' doesn't exist

mysql> Update mycar set status=0 where  id notin (select min(id) as id from mycar as u2 where u2.status=1 group by u2.user);

ERROR 1093 (HY000): You can't specify target table'mycar' for update in FROM clause

不能在FROM子句中查询要更新的目标表'%s'。

一般而言，不能在子查询内查询此时正要更改的表。

 

例如，该限定合用于具有下述形式的语句：

DELETE FROM t WHERE ... (SELECT ... FROM t ...);

UPDATE t ... WHERE col = (SELECT ... FROM t ...);

{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

上面的子查询查询的mycar正是update的表

如果为FROM子句中更改的表使用子查询，讲课避免此问题

mysql> Update mycar set status=0 where  id notin (select id from (select min(id) as id from mycar where status=1 group byuser) as u2);

Query OK, 4 rows affected (0.02 sec)

Rows matched: 4 Changed: 4  Warnings: 0

这是由于FROM中的子查询

已被转化为临时表u2，因此 “mycar”中的相干行已在满足的环境下更新成功。