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