子查询位置MySQL_MySQL子查询三个位置：实例

子查询

SELECT xy,zy,COUNT(xh) AS zs

FROM xuesheng

GROUP BY xy,zy

ORDER BY xy,zy;

SELECT (子查询3位置)     #子查询只能返回单值

FROM (子查询1位置)         #子查询只能表格

WHERE  (子查询2位置)     #子查询只能返回单值或者一列值

### 子查询第一个位置

SELECT

FROM 表格

WHERE

SELECT t.zy,t.zs

FROM (

SELECT xy,zy,COUNT(xh) AS zs

FROM xuesheng

GROUP BY xy,zy

ORDER BY xy,zy) AS t

WHERE t.xy='信息工程学院'  AND t.zs>=100

SELECT * FROM yikatong;

SELECT xm,dz,yelx,xfje FROM yikatong;

SELECT xm,dz,xfje

FROM (

SELECT xm,dz,yelx,xfje FROM yikatong

) AS t

WHERE t.yelx='消费'

SELECT tt.dz,SUM(tt.xfje)

FROM (

SELECT xm,dz,xfje

FROM (

SELECT xm,dz,yelx,xfje FROM yikatong

) AS t

WHERE t.yelx='消费'

) AS tt

GROUP BY tt.dz;

### 子查询第二个位置

SELECT xh,xm,xy,zy,mz

FROM xuesheng

WHERE mz='白族' OR mz='纳西族';

SELECT xh,xm,xy,zy,mz

FROM xuesheng

WHERE mz IN (

'白族',

'纳西族',

'傣族'

);

SELECT xh,xm,xy,zy,mz

FROM xuesheng

WHERE mz ='白族';

SELECT t.mz

FROM (

SELECT mz,COUNT(xh) AS sl

FROM xuesheng

GROUP BY mz

HAVING COUNT(xh)<=5

) AS t

SELECT xh,xm,xy,zy,mz

FROM xuesheng

WHERE mz IN (

SELECT t.mz

FROM (

SELECT mz,COUNT(xh) AS sl

FROM xuesheng

GROUP BY mz

HAVING COUNT(xh)<=5

) AS t

)

ORDER BY mz;

SELECT xh,xm,xy,zy,mz

FROM (

SELECT xh,xm,xy,zy,mz,sr FROM xuesheng

) AS ta

WHERE mz IN (

SELECT t.mz

FROM (

SELECT mz,COUNT(xh) AS sl

FROM xuesheng

GROUP BY mz

HAVING COUNT(xh)<=5

) AS t

)

ORDER BY mz;

### 子查询第三个位置

SELECT xy,zy,xh,xm

FROM xuesheng

WHERE bj='2017级【本科】智能科学与技术1班';

SELECT * FROM xuesheng;

SELECT * FROM zhushuqingkuan WHERE xh='17618900002';

SELECT xs.xy,xs.zy,xs.xh,xs.xm,xs.xh,xs.xh

FROM xuesheng AS xs

WHERE xs.bj='2017级【本科】智能科学与技术1班';

SELECT xs.xy,xs.zy,xs.xh,xs.xm,

(SELECT ld.ldmc FROM zhushuqingkuan AS ld WHERE xs.xh=ld.xh),

(SELECT fh.roomno FROM zhushuqingkuan AS fh WHERE xs.xh=fh.xh)

FROM xuesheng AS xs

WHERE xs.bj='2017级【本科】智能科学与技术1班';