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”中的相干行已在满足的环境下更新成功。