如何进行查询语句的优化
在我的项目中出现这样一个复杂的查询语句,我写成了存储过程,但还是不能进行查询的优化.请教大家如何理解这段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)
3create proc pDrugSell
4 @DrugType varchar(10)
5as
6begin
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
14end
--目的：查询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 aa11
from drug where userdrug.drugid=drugid) as BigName12
from userdrug) xx 13
group by BigName order by cnt desc 14
end 