子查询

子查询与SQL语句分析

关联（correlated）/非关联（uncorrelated）
等值/非等值

from 使用子查询作为数据源

/查询用户名和他们所做的“现金交易”的总额/

select username as 'user name', 
    (case when cashtransactions.totalcash is null then 0
             when cashtransactions.totalcash is  not null then totalcash end) as 'total cash'
from users
left join (select userid, sum(transactionamount) as 'totalcash' from transactions where transactiontype = 'cash' group by userid ) as cashtransactions
on users.userid = cashtransactions.userid
order by users.userid; 

改进与优化
/用函数isnull()代替case/

select username as 'user name', 
       isnull(cashtransactions.totalcash, 0) as 'total cash'
from users
left join (select userid, sum(transactionamount) as 'totalcash' from transactions where transactiontype = 'cash' group by userid ) as cashtransactions
on users.userid = cashtransactions.userid
order by users.userid;   

/标准化语句格式/

select u.username as 'user name', 
       (case when cashtransactions.totalcash is null then 0
          when cashtransactions.totalcash is not null then totalcash end) as 'total cash'
from users u
left join 
    (select userid, sum(transactionamount) as 'totalcash' from transactions where transactiontype = 
    'cash' group by userid) as cashtransactions
on u.userid = cashtransactions.userid;

/公用表表达式/

with cashtransactions as
    (select userid, sum(transactionamount) as 'totalcash' from transactions where transactiontype = 'cash' group by userid )
select username as 'user name', 
      (case when cashtransactions.totalcash is null     then 0
            when cashtransactions.totalcash is not null then totalcash end) as 'total cash'
from users
left join cashtransactions
on users.userid = cashtransactions.userid
order by users.userid;  

select 使用子查询作为一个计算列

/制作表用户以及他们所做的交易的数量/

select username as 'user name', 
       (select count(transactionid) from transactions where users.userid = transactions.userid)             
          as 'number of transactions'
from users 
order by users.userid;

子查询改为连接查询

select u.username as 'user name', 
       count(transactionid) as 'number of transactions'
from users u
left join transactions t
on u.userid = t.userid
group by u.userid, u.username
order by u.userid;

where 在查询条件中使用子查询

1.in
/使用现金交易的用户/

select username as 'user name'
from users
where userid in
        (select userid from transactions where transactiontype = 'cash'); -- 标量

子查询改为连接查询

select u.username as 'user name'
from users u
inner join transactions t
on u.userid = t.userid
where t.transactiontype = 'cash'
group by u.username;  -- 确保每位用户只返回一行记录

2.exists
/查找哪一位用户做了交易/

select username as 'user name'
from users
where exists (select * from transactions where users.userid = transactions.userid);  -- 关联子查询

子查询改为连接查询

select username as 'user name' 
from users
where userid in (select userid from transactions);

select username as 'user name' 
from users u
inner join transactions t
on u.userid = t.userid
group by username;

3.关联子查询
/交易总额少于20美元的用户列表/

select username as 'user name'
from users
where (select sum(transactionamount) from transactions where users.userid = transactions.userid) < 20 ;

子查询改为连接查询

select u.username as 'user name'
from users u
left join transactions t
on u.userid = t.userid
group by u.userid, u.username          -- 关联
having sum(transactionamount) < 20;