select shopid 门店,lcateid 类别,sum(salesum) 金额 from cust
where timea>='20090301' and timea <='20090331'
group by shopid,lcateid 各位大虾,我运行以上语句,得到以下结果: 门店 类别 金额
A 11 500
B 11 507
C 11 560
D 11 870
A 12 540
B 12 350
C 12 290
D 12 890
A 13 569
B 13 548
C 13 540
D 13 698 我想将门店的类别销售进行排名,想得到以下结果,请问怎么写语句
门店 类别 金额 门店类别排名
A 11 500 4
B 11 507 3
C 11 560 2
D 11 870 1
A 12 540 2
B 12 350 3
C 12 290 4
D 12 890 1
A 13 569 2
B 13 548 3
C 13 540 4
D 13 698 1
where timea>='20090301' and timea <='20090331'
group by shopid,lcateid 各位大虾,我运行以上语句,得到以下结果: 门店 类别 金额
A 11 500
B 11 507
C 11 560
D 11 870
A 12 540
B 12 350
C 12 290
D 12 890
A 13 569
B 13 548
C 13 540
D 13 698 我想将门店的类别销售进行排名,想得到以下结果,请问怎么写语句
门店 类别 金额 门店类别排名
A 11 500 4
B 11 507 3
C 11 560 2
D 11 870 1
A 12 540 2
B 12 350 3
C 12 290 4
D 12 890 1
A 13 569 2
B 13 548 3
C 13 540 4
D 13 698 1
select A.门店,A.类别,A.金额,rank()over (partition by A.类别 order by A.金额 desc ) as 门店类别排名
from
(select shopid 门店,lcateid 类别,sum(salesum) 金额 from cust
where timea>='20090301' and timea <='20090331'
group by shopid,lcateid ) A
order by A.类别
from(
select shopid ,lcateid ,sum(salesum) from cust
where timea>='20090301' and timea <='20090331'
group by shopid,lcateid)
试试
ks_reny正解shiyiwan 语句有个地方报错
“over(partition by lcateid order by salesum desc)”里的“salesum”报表的视图不存在
还是很感谢你