数据库查询效率问题

[itit54]

请教各位我的语句是这么写的，很臃肿，最怕效率低下。
所以想问问，这句怎么写效率好一点，谢谢大家了。。。
select sum,borrow_id,username,(select headurl from users where borrows.user_id=users.user_id) as headurl,rating,nickname,title,interestrate,borrowmonth,cast((select sum(sum) from loans where loans.borrow_id=borrows.borrow_id)/sum*100 as int) as hi,(select count(*) from loans where loans.borrow_id=borrows.borrow_id) as loanscount from borrows,user_info where borrows.user_id=user_info.user_id

[SQL77]

改连接查询,

如果要统计的,就先用一个查询代替,再连接查询

[starseeker7]

去掉子查询，那可是一行select一次的家伙
用left join 替换 table,table
当然你数据需要的话直接join也行

因不知道你具体数据结构，所以下面代码也是照你给的，随便改改
你看能不能运行

select sum,borrow_id,username,utb.headurl,rating,nickname,title,interestrate,borrowmonth,ltb.hi,ltb.loanscount
from borrows left join user_info
on borrows.user_id = user_info.user_id
left join
(
select borrow_id,sum(sum) as hi,count(*) as loanscount from loans group by borrow_id
) as ltb on borrows.borrow_id=ltb.borrow_id
left join
(select headurl from users where borrows.user_id=users.user_id) as utb on borrows.user_id=u.user_id

[starseeker7]

啊呀写搂了点，，再改改

select sum,borrows.borrow_id,username,utb.headurl,rating,nickname,title,interestrate,borrowmonth,ltb.hi,ltb.loanscount
 from borrows left join user_info 
on borrows.user_id = user_info.user_id
left join 
(
	select borrow_id,sum(sum) as hi,count(*) as loanscount from loans group by  borrow_id
) as ltb on borrows.borrow_id=ltb.borrow_id
left join 
(select headurl from users where borrows.user_id=users.user_id) as utb on borrows.user_id=utb.user_id

[ACMAIN_CHM]

改成JOIN查询。

[--小F--]

嵌套的效率不高 改成连接查询 如果重复调用的语句比较多的话 可以写入临时表
另外 加索引也可以有效的提高查询速度

[dawugui]

select sum,
       borrow_id,
       username,
       users.headurl,
       rating,
       nickname,
       title,
       interestrate,
       borrowmonth,
       cast((select sum(sum) from loans where loans.borrow_id=borrows.borrow_id)/sum*100 as int) as hi,
       (select count(*) from loans where loans.borrow_id=borrows.borrow_id) as loanscount 
from borrows , user_info ,users
where borrows.user_id=user_info.user_id and borrows.user_id=users.user_id

如果borrows,users的user_id不能一一相对,则不如你原来的语句,不过加上个isnull()比较好.
--假设headurl为字符串型,用isnull((...),''),如果是数值型,用isnull((...),0)
select sum,borrow_id,username,isnull((select headurl from users where borrows.user_id=users.user_id),'') as headurl,rating,nickname,title,interestrate,borrowmonth,cast((select sum(sum) from loans where loans.borrow_id=borrows.borrow_id)/sum*100 as int) as hi,(select count(*) from loans where loans.borrow_id=borrows.borrow_id) as loanscount from borrows,user_info where borrows.user_id=user_info.user_id

[myrroom]

哈哈,都是大牛