如何在MySQL数据库进行子查询

1、where型子查询

（把内层查询结果当作外层查询的比较条件）

#不用order by 来查询最新的商品

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods)

#取出每个栏目下最新的产品(goods_id唯一)

select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id)

2、from型子查询

(把内层的查询结果供外层再次查询)

#用子查询查出挂科两门及以上的同学的平均成绩

思路：

#先查出哪些同学挂科两门以上

select name,count(*) as gk from stu where score <60 having gk >=2

#以上查询结果，我们只要名字就可以了，所以再取一次名字

select name from (select name,count(*) as gk from stu having gk >=2) as t

#找出这些同学了，那么再计算他们的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name

3、exists型子查询

（把外层查询结果拿到内层，看内层的查询是否成立）

#查询哪些栏目下有商品，栏目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id)

mysql

with

as

用法如下：

WITH

AS短语，也叫做子查询部分（subquery

factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION

ALL的不同部分，作为提供数据的部分。

特别对于UNION

ALL比较有用。因为UNION

ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH

AS短语，则只要执行一遍即可。如果WITH

AS短语所定义的表名被调用两次以上，则优化器会自动将WITH

AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH

AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

二．使用方法

先看下面一个嵌套的查询语句：

复制代码

代码如下:

select

*

from

person.StateProvince

where

CountryRegionCode

in

(select

CountryRegionCode

from

person.CountryRegion

where

Name

like

'C%')

上面的查询语句使用了一个子查询。虽然这条SQL语句并不复杂，但如果嵌套的层次过多，会使SQL语句非常难以阅读和维护。因此，也可以使用表变量的方式来解决这个问题，SQL语句如下：

复制代码

代码如下:

declare

@t

table(CountryRegionCode

nvarchar(3))

insert

into

@t(CountryRegionCode)

(select

CountryRegionCode

from

person.CountryRegion

where

Name

like

'C%')

select

*

from

person.StateProvince

where

CountryRegionCode

in

(select

*

from

@t)

虽然上面的SQL语句要比第一种方式更复杂，但却将子查询放在了表变量@t中，这样做将使SQL语句更容易维护，但又会带来另一个问题，就是性能的损失。由于表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。为此，在SQL

Server

2005中提供了另外一种解决方案，这就是公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。

下面是CTE的语法：

复制代码

代码如下:

[

WITH

<common_table_expression>

[

,n

]

]

<common_table_expression>::=

expression_name

[

(

column_name

[

,n

]

)

]

AS

(

CTE_query_definition

)

现在使用CTE来解决上面的问题，SQL语句如下：

复制代码

代码如下:

with

cr

as

(

select

CountryRegionCode

from

person.CountryRegion

where

Name

like

'C%'

)

select

*

from

person.StateProvince

where

CountryRegionCode

in

(select

*

from

cr)

其中cr是一个公用表表达式，该表达式在使用上与表变量类似，只是SQL

Server

2005在处理公用表表达式的方式上有所不同。

mysql> select * from score_t

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

| name     | sub_id | sub_name | score |

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

| haiyan   |      1 | 语文     |    99 |

| 3306     |      1 | 语文     |    50 |

| 村上春树 |      1 | 语文     |    80 |

| 雨果     |      1 | 语文     |    85 |

| 牛顿     |      2 | 数学     |    98 |

| 阿基米德 |      2 | 数学     |    90 |

| 霍金     |      2 | 数学     |    92 |

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

7 rows in set

mysql> select score_t.* , tab.avg_score from score_t ,

(select sub_id,avg(score) as avg_score from score_t where sub_id =1 ) tab 

where tab.sub_id = score_t.sub_id and score_t.score > tab.avg_score

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

| name     | sub_id | sub_name | score | avg_score |

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

| haiyan   |      1 | 语文     |    99 |      78.5 |

| 村上春树 |      1 | 语文     |    80 |      78.5 |

| 雨果     |      1 | 语文     |    85 |      78.5 |

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

3 rows in set

mysql> select score_t.* , tab.avg_score from score_t ,

(select sub_id,avg(score) as avg_score from score_t group by sub_id) tab 

where tab.sub_id = score_t.sub_id and score_t.score > tab.avg_score

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

| name     | sub_id | sub_name | score | avg_score         |

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

| haiyan   |      1 | 语文     |    99 |              78.5 |

| 村上春树 |      1 | 语文     |    80 |              78.5 |

| 雨果     |      1 | 语文     |    85 |              78.5 |

| 牛顿     |      2 | 数学     |    98 | 93.33333333333333 |

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

4 rows in set

mysql>

---