如何进行查询语句的优化
在我的项目中出现这样一个复杂的查询语句,我写成了存储过程,但还是不能进行查询的优化.请教大家如何理解这段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
--目的：查询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 
浙公网安备 33010602011771号