SQL 分组 加列 加自编号 自编号限定

说明：

(1)日期以年月形式显示：convert(varchar(7),字段名,120) ,

(2)加一列

(3)自编号：

row_number() over(order by 字段名 desc) as RowID

row_number() over(partition by 字段1 order by 字段2) as RowID

(4)自编号的限制(不可直接在WHERE条件中加)

举例说明：

想要达到的效果：按月统计各工种的前5名(以件数为依据)

初始SQL语句：

select sum(Sum_TrueNum) 件数,PickUser_Name 拣货人,convert(varchar(7),PickTime,120) 时间
from V_OutIn_MainTop 
where Brand_ID=66 and ClassReport_Name=‘出库‘ and len(PickUser_Name)>0 
and PickTime>=‘2014-9-1‘ and PickTime<‘2014-12-1‘
group by PickUser_Name,convert(varchar(7),PickTime,120)

效果图：

第一步：    加一列

SQL语句：

select 工种=‘拣货‘,sum(Sum_TrueNum) 件数,PickUser_Name 拣货人,convert(varchar(7),PickTime,120) 时间
from V_OutIn_MainTop 
where Brand_ID=66 and ClassReport_Name=‘出库‘ and len(PickUser_Name)>0 
and PickTime>=‘2014-9-1‘ and PickTime<‘2014-12-1‘
group by PickUser_Name,convert(varchar(7),PickTime,120)

效果图：

第二步：加自编号(按月、按人分组排序)

SQL语句：

select 工种=‘拣货‘,row_number() over(partition by convert(varchar(7),PickTime,120) order by sum(Sum_TrueNum) desc)as RowID,
sum(Sum_TrueNum) 件数,PickUser_Name 拣货人,convert(varchar(7),PickTime,120) 时间
from V_OutIn_MainTop 
where Brand_ID=66 and ClassReport_Name=‘出库‘ and len(PickUser_Name)>0 
and PickTime>=‘2014-9-1‘ and PickTime<‘2014-12-1‘
group by PickUser_Name,convert(varchar(7),PickTime,120)

效果图：

第三步：自编号限定(只取前几名)

SQL语句：

with rowJianHuo
as
(
select  工种=‘拣货‘,row_number() over(partition by convert(varchar(7),PickTime,120) order by sum(Sum_TrueNum) desc)as RowID, 
sum(Sum_TrueNum) 件数,PickUser_Name 拣货人,convert(varchar(7),PickTime,120) 时间
from V_OutIn_MainTop 
where Brand_ID=66 and ClassReport_Name=‘出库‘ and len(PickUser_Name)>0 
and PickTime>=‘2014-9-1‘ and PickTime<‘2014-12-1‘
group by PickUser_Name,convert(varchar(7),PickTime,120)
)
select * from rowJianHuo where RowID<6

也可以这样写：

select * from
(
select  工种=‘拣货‘,row_number() over(partition by convert(varchar(7),PickTime,120) order by sum(Sum_TrueNum) desc)as RowID, 
sum(Sum_TrueNum) 件数,PickUser_Name 拣货人,convert(varchar(7),PickTime,120) 时间
from V_OutIn_MainTop 
where Brand_ID=66 and ClassReport_Name=‘出库‘ and len(PickUser_Name)>0 
and PickTime>=‘2014-9-1‘ and PickTime<‘2014-12-1‘
group by PickUser_Name,convert(varchar(7),PickTime,120)
)a 
where RowID<6

效果图：(数据有点少，所以数据没有变化)

备注：

两个表，结构相同，连成一张表：用

select * from 表1

union all

select * from 表2