一个select语句的查询结果可以作为另一个语句的输入值，可分为以下三种情况：

• 子查询出现在where子句中，作为过滤条件

select 列名
from 表名
where 列 操作符 (select 列名 from 表名)

SELECT *
from dw.fct_sales
where dimDateID ='20170702'
and dimMemberID  in 
(SELECT dimMemberID
from dw.fct_sales
where dimDateID ='20170702'
      and dimMemberID <>0
group by dimMemberID
having sum(AMT) between 100 and 200)

• 子查询出现在from 子句中，作为一个临时表使用，此时临时表一定要命名

select 列名
from (select 列名 from 表名)

select dimMemberID 
       ,money 
       ,case when money<100 then 'D'
       when money>=100 and money<500 then 'C'
       when money>=500 and money<1000 then 'B'
       when money>=1000 then 'A'
       end as type_money
from 
(SELECT  dimMemberID 
        ,sum(AMT) as money
from dw.fct_sales
where dimDateID ='20170702'
and dimMemberID <>0
group by dimMemberID ) temp

• 子查询出现在select list中，作为一个字段值来返回，有且只能有一个字段值返回

select 列名,(select 列名 from 表名)
from 表名
where 列 操作符

SELECT dimMemberID
       ,sum(AMT) as money
       ,(SELECT sum(AMT) from dw.fct_sales where dimDateID ='20170702' and dimMemberID <>0) as sum_money
       ,CONCAT(round(sum(AMT)/(SELECT sum(AMT) from dw.fct_sales where dimDateID ='20170702' and dimMemberID <>0),4)*100,'%') as money_rate
from dw.fct_sales
where dimDateID ='20170702'
      and dimMemberID <>0
group by dimMemberID