为查询出的结果新增一列排名

低代码开发平台 2008-11-19 09:03:03 
业务员销售业绩排名：

//按制单人员的制单金额进行汇总排名

有如下结构的表

Create table BillUnion 

(

 MakeTime DateTime,/*制单时间*/

 ActionType varchar(20),/*活动类型*/

 BillType varchar(10),/*单据大类型*/

 SmallType varchar(50),/*单据小类型*/

 BillID varchar(20),/*单据号*/

 TotalMoney float, /*单据金额*/

 OrganizationID int,/*公司代码*/

 EmployeePP varchar(20)/*制单人*/

)

为了便于大家测试，我插入几个记录：

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-1','收款','经营活动','商品销售','SPXS2008010101',100,1,'小刘')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-1','收款','经营活动','商品销售','SPXS2008010102',150,1,'小王')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-1','付款','经营活动','商品销售','SPCG2008010101',200,1,'小王')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-2','付款','经营活动','商品销售','SPCG2008010201',100,1,'小刘')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-2','收款','经营活动','商品销售','SPXS2008010201',100,1,'小刘')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-2','付款','经营活动','费用报销','FYBX2008010201',50,1,'小王')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-3','付款','经营活动','费用报销','SPCG2008010301',150,1,'小王')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-3','收款','经营活动','费用报销','SPXS2008010301',500,1,'小刘')



Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-3','付款','经营活动','费用报销','FYBX2008010301',150,1,'小王')





Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-4','付款','经营活动','商品采购','SPCG2008010401',100,1,'小王')



select * from BillUnion



drop table BillUnion



用下面的sql语句查询得到销售业绩由高到低，但我还想得到排名。

select top 100 percent EmployeePP,sum(totalmoney)as summoney,max(MakeTime)as maxtime  from BillUnion group by EmployeePP order by sum(totalmoney) desc,max(MakeTime)

有位前辈给了2005下的sql语句
select EmployeePP,summoney,row_num from (

select top 100 percent EmployeePP,sum(totalmoney)as summoney,max(MakeTime)as maxtime,row_number() over(order by sum(totalmoney)desc,max(MakeTime) ) as row_num  from BillUnion group by EmployeePP order by sum(totalmoney) desc,max(MakeTime)

)T
，但我想得到2000下的sql语句，希望高人指点。
...全文
94 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
低代码开发平台 2008-11-20
  • 打赏
  • 举报
 回复 
解决的办法：

select identity(int,1,1) as ID,b.* into #Temp from (select top 100 percent EmployeePP,sum(totalmoney)as summoney,max(MakeTime)as maxtime  from BillUnion 

group by EmployeePP order by sum(totalmoney) desc,max(MakeTime))b



select * from #Temp
-晴天 2008-11-19
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 liangCK 的回复:]
WHERE 金额>a.金额
OR (金额=a.金额
AND 时间<a.时间)
[/Quote]
-晴天 2008-11-19
  • 打赏
  • 举报
 回复
不是刚贴到那个贴子上了嘛!
Create table BillUnion 

(

 MakeTime DateTime,/*制单时间*/

 ActionType varchar(20),/*活动类型*/

 BillType varchar(10),/*单据大类型*/

 SmallType varchar(50),/*单据小类型*/

 BillID varchar(20),/*单据号*/

 TotalMoney float, /*单据金额*/

 OrganizationID int,/*公司代码*/

 EmployeePP varchar(20)/*制单人*/

)

--为了便于大家测试，我插入几个记录：

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-1','收款','经营活动','商品销售','SPXS2008010101',100,1,'小刘')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-1','收款','经营活动','商品销售','SPXS2008010102',150,1,'小王')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-1','付款','经营活动','商品销售','SPCG2008010101',200,1,'小王')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-2','付款','经营活动','商品销售','SPCG2008010201',100,1,'小刘')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-2','收款','经营活动','商品销售','SPXS2008010201',100,1,'小刘')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-2','付款','经营活动','费用报销','FYBX2008010201',50,1,'小强')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-3','付款','经营活动','费用报销','SPCG2008010301',150,1,'小王')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-3','收款','经营活动','费用报销','SPXS2008010301',500,1,'小刘')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-3','付款','经营活动','费用报销','FYBX2008010301',150,1,'小强')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-4','付款','经营活动','商品采购','SPCG2008010401',100,1,'小强')

Insert into BillUnion(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 

Values('2008-1-3','付款','经营活动','费用报销','FYBX2008010305',200,1,'小强')

select EmployeePP,summoney,rownum=(select count(*)+1 from (

    select EmployeePP,sum(totalmoney) as summoney,max(maketime) as maxtime from billunion group by employeepp

)b where summoney>t.summoney  or (summoney=t.summoney and maxtime<t.maxtime)) from (

select EmployeePP,sum(totalmoney) as summoney,max(maketime) as maxtime from billunion group by employeepp

)T

order by rownum

--select EmployeePP,summoney,row_num from (

--select top 100 percent EmployeePP,sum(totalmoney)as summoney,max(MakeTime)as maxtime,row_number() over(order by sum(totalmoney)desc,max(MakeTime) ) as row_num  from BillUnion group by EmployeePP order by sum(totalmoney) desc,max(MakeTime)

--)T

go

drop table BillUnion

/*

EmployeePP           summoney               rownum

-------------------- ---------------------- -----------

小刘                   800                    1

小王                   500                    2

小强                   500                    3

*/
liangCK 2008-11-19
  • 打赏
  • 举报
 回复
例如
姓名 金额 时间
小刘 40 2008-10-10
小刘 30 2008-10-09
小刘 20 2008-10-08
小强 10 2008-10-07


我想请问..聚合之后.小刘是要哪个时间呢?.
fabric区块链 2008-11-19
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 stone0419 的回复:]
SQL codeSELECT
EmployeePP,
金额,
单据小类型,
排名=(SELECT COUNT(*)+1
FROM(
SELECT
EmployeePP,
SUM(TotalMoney) AS 金额,
SmallType AS 单据小类型
FROM BillUnion
WHERE SmallType='商品销售'
GROUP BY
EmployeePP,
SmallType
…
[/Quote]
这个可以！！！！
fabric区块链 2008-11-19
  • 打赏
  • 举报
 回复
小刘 100.0 商品销售 2008-01-01 00:00:00.000 3
小王 350.0 商品销售 2008-01-01 00:00:00.000 1
小刘 200.0 商品销售 2008-01-02 00:00:00.000 2

这是运行结果,小刘出现两次？？
fabric区块链 2008-11-19
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 liangCK 的回复:]
SQL codeSELECT
EmployeePP,
金额,
单据小类型,
时间,
排名=(SELECT COUNT(*)+1
FROM(
SELECT
EmployeePP,
SUM(TotalMoney) AS 金额,
SmallType AS 单据小类型,
MakeTime AS 时间
FROM BillUnion
WHERE SmallType='商品销售'
GROUP BY
…
[/Quote]
好像不行吧！
liangCK 2008-11-19
  • 打赏
  • 举报
 回复 
SELECT 

    EmployeePP,

    金额,

    单据小类型,

    时间,

    排名=(SELECT COUNT(*)+1

          FROM(

              SELECT 

                 EmployeePP,

                 SUM(TotalMoney) AS 金额,

                 SmallType AS 单据小类型,

                 MakeTime AS 时间

              FROM BillUnion 

              WHERE SmallType='商品销售'

              GROUP BY 

                 EmployeePP,

              SmallType,

              MakeTime

          ) AS t

          WHERE 金额>a.金额

            OR (金额=a.金额 

               AND 时间<a.时间)

          )

FROM(

    SELECT 

        EmployeePP,

        SUM(TotalMoney) AS 金额,

        SmallType AS 单据小类型,

        MakeTime AS 时间

    FROM BillUnion 

    WHERE SmallType='商品销售'

    GROUP BY 

        EmployeePP,

        SmallType,

        MakeTime

) AS a
liangCK 2008-11-19
  • 打赏
  • 举报
 回复
应该是or 金额=a.金额
liangCK 2008-11-19
  • 打赏
  • 举报
 回复 
SELECT 

    EmployeePP,

    金额,

    单据小类型,

    时间,

    排名=(SELECT COUNT(*)+1

          FROM(

              SELECT 

                 EmployeePP,

                 SUM(TotalMoney) AS 金额,

                 SmallType AS 单据小类型,

                 MakeTime AS 时间

              FROM BillUnion 

              WHERE SmallType='商品销售'

              GROUP BY 

                 EmployeePP,

              SmallType,

              MakeTime

          ) AS t

          WHERE 金额>a.金额

            OR (金额>a.金额 

               AND 时间<a.时间)

          )

FROM(

    SELECT 

        EmployeePP,

        SUM(TotalMoney) AS 金额,

        SmallType AS 单据小类型,

        MakeTime AS 时间

    FROM BillUnion 

    WHERE SmallType='商品销售'

    GROUP BY 

        EmployeePP,

        SmallType,

        MakeTime

) AS a
liangCK 2008-11-19
  • 打赏
  • 举报
 回复
我想问问
当你FROM里进行聚合金额分组求和后.你是要得到哪一个时间?
liangCK 2008-11-19
  • 打赏
  • 举报
 回复 
WHERE 金额>a.金额

     OR (金额>a.金额 

          AND 时间<a.时间)
水族杰纶 2008-11-19
  • 打赏
  • 举报
 回复
按照sum(totalmoney)as summoney 排名？
低代码开发平台 2008-11-19
  • 打赏
  • 举报
 回复 
SELECT 

    EmployeePP,

    金额,

    单据小类型,

    排名=(SELECT COUNT(*)+1

          FROM(

              SELECT 

                 EmployeePP,

                 SUM(TotalMoney) AS 金额,

                 SmallType AS 单据小类型

              FROM BillUnion 

              WHERE SmallType='商品销售'

              GROUP BY 

                 EmployeePP,

              SmallType

          ) AS t

          WHERE 金额>a.金额

          )

FROM(

    SELECT 

        EmployeePP,

        SUM(TotalMoney) AS 金额,

        SmallType AS 单据小类型

    FROM BillUnion 

    WHERE SmallType='商品销售'

    GROUP BY 

        EmployeePP,

        SmallType

) AS a

有位前辈给的是这个，但是但销售金额一样的没有按时间早的排在前面。也就是说排名不要有一样的
SQL 给查询数据新增一个列，并自定义分类排序
SQL查询数据时新增一个列，并按照某个字段值进行分类排序
dataframe新增一列的方法
先上结论 df['新增列名']=list#list为你想添加的数据 当然，dataframe新增一列的方法很多，但要注意的是 df.append(new_df1) df.append(new_df2) 使用append方法添加列时，必须保证新增的df没有相同的列名，否则就会覆盖，如果你想把很多相同列名的数据合并到一个df里，这种方法不适合。 ...
Oracle增加一个排名列（序号列）
Oracle查询时增加一个排名列，对用户活跃率进行排名：select row_number() over(order by act_rate desc) rank,act_rate from act_rate_table;注：rank：排名列别称act_rate：用户活跃率...
【MySQL】MySQL中给查询记录设置列序号，排名
有时候需要给查询出来的记录每行都给他加一个序号。 核心sql语句 SELECT id, @rownum := @rownum + 1 AS rank FROM bp_user bu, ( SELECT @rownum := 0 ) AS t ORDER BY id DESC
增加排名列SQL语句（需排名的列值相等时排名相同）
SQL 查询数据时按某列排序后增加排名列，需排名的列值相等时排名相同，即如需排名列数组为：9，9，8，7，7，6     添加的排名列数组需要显示为两种： 　　第一种：1，1，3，4，4，6 （这种排名方法应该是最普遍的排名方法啦） 或者 　　第二种：1，1，2，3，3，4 （某些特殊情况需要） --现在假设测试数据:创建临时表 #score 并插入数据  creat
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章