我要统计一个表,按门店统计销售前500位;
表结构为:部门、编码、数量、金额;
如果是按编码统计可以这样:select top 500 编码,sum(数量),sum(金额) from diandan group by 编码 order by sum(金额)desc
如果统计部门和编码统计怎么写语句,
表结构为:部门、编码、数量、金额;
如果是按编码统计可以这样:select top 500 编码,sum(数量),sum(金额) from diandan group by 编码 order by sum(金额)desc
如果统计部门和编码统计怎么写语句,
from diandan
group by 部门 , 编码
order by sum(金额)desc
from diandan
group by 部门 , 编码
order by sum(金额)desc
--1
select t.* from tb t where 金额 in (select top 500 金额 from tb where 部门 = t.部门 order by 金额 desc)--2
select m.* from
(
select 部门,编码,sum(数量) 数量,sum(金额) 金额 from tb group by 部门,编码
) m where 金额 in
(select top 500 * 金额 from
(
select 部门,编码,sum(数量) 数量,sum(金额) 金额 from tb group by 部门,编码
) n
where n.部门 = m.部门 order by n.金额 desc)