子查询与SQL语句分析

子查询与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 
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;