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
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