高级查询教学用例

select * from typeinfo select * from parter select * from provider select * from stock select * from stockdetail -----1111 select * from parter where tid=(select tid from typeinfo where tname='核心部件') -----22222 select tid,count(*) 数量 from parter group by tid ---分类 select tid from typeinfo ---结合上述 select * from typeinfo T left join parter P on T.tid=P.tid select T.tid from typeinfo T left join parter P on T.tid=P.tid group by T.tid select test.tid,count(test.pid) from (select T.tid,P.pid from typeinfo T left join parter P on T.tid=P.tid) as test group by test.tid --> select T.tid, count(P.pid) from typeinfo T left join parter P on T.tid=P.tid group by T.tid ---------33333 --下过订单的供应商 select distinct(proid) from stock --没下订单的 select * from provider where proid not in (select distinct(proid) from stock) ----------444444 --公式:采购总数量= 部件零件数*采购数量*(100+部件损失率)/100 --查出被采购的PID --采用分步 --select pid from stockdetail order by pid --select pid,sum(sdnum) from stockdetail group by pid --select * from parter P inner join -- (select pid,sum(sdnum) as num from stockdetail group by pid) test -- on P.pid=test.pid select pid,total=(pinclunum*num*(100+prate)/100) from (select P.*,num from parter P inner join (select pid,sum(sdnum) as num from stockdetail group by pid) test on P.pid=test.pid) test2 order by total desc ------ --select * from parter P inner join stockdetail S on P.pid=S.pid order by P.pid select P.pid, total=sum(pinclunum*sdnum*(100+prate)/100) from parter P inner join stockdetail S on P.pid=S.pid group by P.pid order by total desc ---- select StockDetail.PID,sum((PIncluNum*SDNum*(100+PRate)/100)) ts from dbo.StockDetail inner join dbo.Parter on StockDetail.PID=Parter.PID group by StockDetail.PID order by ts desc /* ---555555 --获取采购数量最大的采购单信息 select * from parter P inner join stockdetail S on P.pid=S.pid order by P.pid select sdid,total=(pinclunum*sdnum*(100+prate)/100) from parter P inner join stockdetail S on P.pid=S.pid order by total desc select top 1 sdid,total=(pinclunum*sdnum*(100+prate)/100) from parter P inner join stockdetail S on P.pid=S.pid order by total desc ----结合后 select * from stockdetail where sdid = (select top 1 sdid from parter P inner join stockdetail S on P.pid=S.pid order by (pinclunum*sdnum*(100+prate)/100) desc) --有问题, 如果有两个订单的总数量相等怎么办? select * from stockdetail where sdid in (select sdid from parter P inner join stockdetail S on P.pid=S.pid where pinclunum*sdnum*(100+prate)/100= (select total=max(pinclunum*sdnum*(100+prate)/100) from parter P inner join stockdetail S on P.pid=S.pid)) */ ---555555 --获取采购数量最大的采购单信息 select * from Stock inner join (select top 1 sum((PIncluNum*SDNum*(100+PRate)/100)) ts, StockDetail.SID from dbo.StockDetail inner join dbo.Parter on StockDetail.PID=Parter.PID inner join Stock on StockDetail.SID=Stock.SID group by StockDetail.SID order by ts desc) tempdt on Stock.SID=tempdt.SID --采购单 select * from stockdetail order by sid -- select * from stockdetail S inner join parter P on S.pid=P.pid order by S.sid -- select S.sid, S.sdnum, P.pid, P.pinclunum, P.prate from stockdetail S inner join parter P on S.pid=P.pid order by S.sid select S.sid, total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid order by total desc -- select top 1 S.sid, total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid order by total desc --结合后 select * from stock inner join ( select top 1 S.sid, total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid order by total desc ) test on stock.sid=test.sid --有问题, 如果有两个订单的总数量相等怎么办? --利用最大数, 而不用排序后的第一条记录 select top 1 total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid order by total desc --最大的 sid select test.sid from ( select S.sid, total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid ) test where test.total=( select top 1 total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid order by total desc ) -- select * from stock inner join ( select test.sid,test.total from ( select S.sid, total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid ) test where test.total=( select top 1 total=sum((PIncluNum*SDNum*(100+PRate)/100)) from stockdetail S inner join parter P on S.pid=P.pid group by S.sid order by total desc ) ) test1 on stock.sid=test1.sid -----66666666 ---获取采购量最大的部件信息 --各部件采购量, 从大到小排序 select P.pid,total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join stockdetail S on S.pid=P.pid group by P.pid order by total desc -- 选出 pid select test1.pid from ( select P.pid,total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join stockdetail S on S.pid=P.pid group by P.pid ) test1 where test1.total= ( select top 1 total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join stockdetail S on S.pid=P.pid group by P.pid order by total desc ) --选出信息 select * from parter where pid= ( select test1.pid from ( select P.pid,total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join stockdetail S on S.pid=P.pid group by P.pid ) test1 where test1.total= ( select top 1 total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join stockdetail S on S.pid=P.pid group by P.pid order by total desc ) ) ------- ----666666666 ----获取2008年1月采购量最大的部件信息 --- 获取2008年1月的采购单 select S1.sid,S1.sdate,S2.pid,S2.sdnum from stock S1 inner join stockdetail S2 on S1.sid=S2.sid where datediff(mm,'2008-1-1',S1.sdate)=0 --- 2008年1月被采购的部件 select * from parter P inner join ( select S1.sid,S1.sdate,S2.pid,S2.sdnum from stock S1 inner join stockdetail S2 on S1.sid=S2.sid where datediff(mm,'2008-1-1',S1.sdate)=0 ) test1 on P.pid=test1.pid order by P.pid --各部件的采购量 select P.pid,total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join ( select S1.sid,S1.sdate,S2.pid,S2.sdnum from stock S1 inner join stockdetail S2 on S1.sid=S2.sid where datediff(mm,'2008-1-1',S1.sdate)=0 ) test1 on P.pid=test1.pid group by P.pid --采购量最多的 select P.pid,total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join ( select S1.sid,S1.sdate,S2.pid,S2.sdnum from stock S1 inner join stockdetail S2 on S1.sid=S2.sid where datediff(mm,'2008-1-1',S1.sdate)=0 ) test1 on P.pid=test1.pid group by P.pid order by total desc --部件信息 select * from parter where pid in ( select pid from ( select P.pid,total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join ( select S1.sid,S1.sdate,S2.pid,S2.sdnum from stock S1 inner join stockdetail S2 on S1.sid=S2.sid where datediff(mm,'2008-1-1',S1.sdate)=0 ) test1 on P.pid=test1.pid group by P.pid ) test2 where test2.total= ( select top 1 total=sum((PIncluNum*SDNum*(100+PRate)/100)) from parter P inner join ( select S1.sid,S1.sdate,S2.pid,S2.sdnum from stock S1 inner join stockdetail S2 on S1.sid=S2.sid where datediff(mm,'2008-1-1',S1.sdate)=0 ) test1 on P.pid=test1.pid group by P.pid order by total desc ) ) ----------- select Parter.*,ts from Parter inner join ( select TOP 1 sum(PIncluNum*SDNum*(100+PRate)/100) ts, StockDetail.PID from dbo.