如何进行查询语句的优化
在我的项目中出现这样一个复杂的查询语句,我写成了存储过程,但还是不能进行查询的优化.请教大家如何理解这段SQL语句的执行过程和顺序.谢谢
1.第一张表的关键字段
select sellid,drugid from userdrug
2.第二张表的关键字段
select drugid,bigid from drug
3.第三张表的关键字段
select bigid,bigname from drugbig
1.第一张表的关键字段
select sellid,drugid from userdrug
2.第二张表的关键字段
select drugid,bigid from drug
3.第三张表的关键字段
select bigid,bigname from drugbig
1
--目的:查询drugid归属大类所提供药品的信息条数
2
--输入参数:输入药品的类别:中成药还是西药,还是中草药
--结 果: bigname,bigid,count(sellid)
3
create proc pDrugSell
4
@DrugType varchar(10)
5
as
6
begin
7
select count(*)as cnt,BigName
8
from (select
9
(select
10
(select bigname from drugbig where bigid=drug.bigid and DrugType =@DrugType) as aa
11
from drug where userdrug.drugid=drugid) as BigName
12
from userdrug) xx
13
group by BigName order by cnt desc
14
end
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
--结 果: bigname,bigid,count(sellid)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)