|
我建立了一个查询,将年初以来的净销售额恢复到最近完成的月份。查询联合来自发票和贷款凭证的总额。效果很好。我在另一个工具中使用查询。工具按卡代码求和,并允许我进行有趣的操作。这是查询:! D( u& k/ ~& p* m m# F8 n
select x.cardcode, x.customer, case when x.rep is null then (select slpname from ocrd inner join oslp on ocrd.slpcode = oslp.slpcode where ocrd.cardcode = x.cardcode) else x.rep end as rep, x.city, x.state, x.country, case when isnumeric(x.total) = 0 then else x.total end as [net total], x.docdatefrom ( select t0.cardcode as cardcode, t0.[cardname] as customer, t1.city as city, t1.state as state, t1.country as country, t4.slpname as rep, sum(t3.linetotal) - t2.discsum as total, t2.docdate as [docdate] from ocrd t inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) left outer join oinv t2 on t0.cardcode = t2.cardcode left outer join inv1 t3 on t2.docentry = t3.docentry left outer join oslp t4 on t2.slpcode = t4.slpcode where t0.[cardtype] = 'C' and t1.adrestype = 'S' group by t0.cardcode,t0.cardname,t1.city,t1.state,t1.country,t4.slpname,t2.discsum,t2.docdate union all select t0.cardcode as cardcode, t0.cardname as customer, t1.city as city, t1.state as state, t1.country as country, t4.slpname as rep, -1*(sum(t3.linetotal) - t2.discsum) as total, t2.docdate from ocrd t inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) left outer join orin t2 on t0.cardcode = t2.cardcode left outer join rin1 t3 on t2.docentry = t3.docentry left outer join oslp t4 on t2.slpcode = t4.slpcode where t0.[cardtype] = 'C' and t1.adrestype = 'S' group by t0.cardcode, t0.cardname, t1.city, t1.state, t1.country, t4.slpname, t2.discsum, t2.docdate) xwhere (x.docdate between and dateadd(day,-1,'2008/' cast(month(getdate()) as varchar(2) '/01'or x.docdate between and dateadd(day,-2009/ cast(month(getdate()) as varchar(2) '/01' or x.docdate between '2010/01/01' and dateadd(day,-2010/ cast(month(getdate()) as varchar(2) /01)group by x.cardcode,x.customer,x.rep,x.city,x.state,x.country,x.total,x.docdate现在,我想修改查询,以返回每年的客户净额n个,例如20%。这是我遇到麻烦的地方。我在用它。SQL$ d3 X. s1 @' Z f8 o" t$ p
Server,所以首先,我想我会尝试使用它row_number()over(partition .... )。这是我的第一次尝试:
, B" j. ?+ c0 a/ O5 s4 tselect m.Cardcode,m.Customer,m.Rep,m.City,m.State,m.Country,m.Nettotal as 'Net Total',m.docdate as 'Posting Date'from ( select t.cardcode,t.customer,t.rep,t.city,t.state,t.country,t.nettotal,t.docdate,row_number() over(partition by t.docdate order by t.nettotal desc) as rownum from ( select x.cardcode, x.customer, case when x.rep is null then (select slpname from ocrd inner join oslp on ocrd.slpcode = oslp.slpcode where ocrd.cardcode = x.cardcode) else x.rep end as rep, x.city, x.state, x.country, case when isnumeric(x.total) = 0 then else x.total end as nettotal, x.docdate from ( select t0.cardcode as cardcode, t0.[cardname] as customer, t1.city as city, t1.state as state, t1.country as country, t4.slpname as rep, sum(t3.linetotal) - t2.discsum as total, t2.docdate as docdate from ocrd t0 inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) left outer join oinv t2 on t0.cardcode = t2.cardcode left outer join inv1 t3 on t2.docentry = t3.docentry left outer join oslp t4 on t2.slpcode = t4.slpcode where t0.[cardtype] = 'C' and t1.adrestype = 'S group by t0.cardcode, t0.cardname, t1.city, t1.state, t1.country, t4.slpname, t2.discsum, t2.docdate union all select t0.cardcode as cardcode, t0.cardname as customer, t1.city as city, t1.country as country, t1.state as state, t4.slpname as rep, -1*(sum(t3.linetotal) - t2.discsum) as total, t2.docdate from ocrd t inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) left outer join orin t2 on t0.cardcode = t2.cardcode left outer join rin1 t3 on t2.docentry = t3.docentry left outer join oslp t4 on t2.slpcode = t4.slpcode where t0.[cardtype] = 'C' and t1.adrestype = 'S group by t0.cardcode, t0.cardname, t1.city, t1.state, t1.country, t4.slpname, t2.discsum, t2.docdate) x where (x.docdate between and dateadd(day,-2008/ cast(month(getdate()) as varchar or x.docdate between and dateadd(day,-2009/ cast(month(getdate()) as varchar or x.docdate between and dateadd(day,-2010/ cast(month(getdate()) as varchar(2)) '/01')) group by x.cardcode, x.customer, x.rep, x.city, x.state, x.country, x.total, x.docdate) as t) as mwhere rownum 即使我做对了,走这条路还是很麻烦的,因为它不允许我得到它之前n只有一个百分点,才能得到之前的百分点n个。
% Q9 {, f; y4 T4 N- _我还没有尝试使用交叉应用程序或子选择来达到我想要的结果。
& E; q/ Q% ^5 M# {有人能帮我解决这个问题吗?此外,如何编写它可能不是很有效,硬编码日期范围的选择也不是一个好的解决方案。我认为有很多事情需要改进:)' n8 X; ~5 L( q# G3 [& ~
感谢您的帮助。
6 \8 J! p8 @7 Z
. I4 l9 p/ l0 \- C$ x( d | 解决方案: 0 F$ K6 h: c' s0 n o& Y
您可以在SQL Server使用排名功能-但不能,ROW_NUMBER()但可以使用NTILE()。6 u9 ~! s" F L
NTILE()将结果集分解成您指定的尽可能多的数据块-因为你想排在前20名,你想排在前20名%,所以你可以用NTILE(5)。0 P2 I9 ^% a3 r
因此,您的CTE它应该看起来像这样:
& N" K @' j/ ZWITH CustomerPerYear AS( SELECT c.Name,s.Sales, NTILE(5) OVER (PARTITION BY c.CustomerID ORDER BY s.Amount DESC) as 'NTile' FROM dbo.Customer c INNER JOIN dbo.Sales s ON s.CustomerID = c.CustomerID)SELECT *FROM CustomerPerYearWHERE NTile = 1因此,基本上,您根据客户划分数据,然后根据销售量将每个客户的销售排名为5个NTile组。对于每个客户,NTILE = 1是销售额前20名%。
: c/ b' E6 [% i* b, l0 [如有必要,请参考NTILE上的MSDN获取更多详细信息的文档。 |
|