如何取分组后取最大的一条记录

[linghengmao]

各位大虾，比如我有以下这张表：

姓名 销售金额 销售时间 零售单号 店名
------------------------------------------------------------------------------------------
张三 60 2014-9-1 140901135413 a店
李四 40 2014-9-1 140901135413 a店
王五 120 2014-9-9 1409091841615 a店
周十 60 2014-9-2 1409021327266 b店
王五 60 2014-9-2 1409021327266 b店
赵六 818 2014-9-4 1409041612681 b店

我想取每一张零售单号的最大的那条记录，但销售金额为整张零售金额的值，例如零售单号：140901135413，
这里它有两条记录，我想得到的结果是：
张三 100 2014-9-1 140901135413 a店
它的销售金额为张三和李四之和，因为它是同一单号。
周十，王五也是同一零售单号，但销售金额是一样的，随机取一条就行，则为：
周十 120 2014-9-2 1409021327266 b店

最后的结果则为：
姓名 销售金额 销售时间 零售单号 店名
------------------------------------------------------------------------------------------
张三 100 2014-9-1 140901135413 a店
王五 120 2014-9-9 1409091841615 a店
周十 120 2014-9-2 1409021327266 b店
赵六 818 2014-9-4 1409041612681 b店

还请各位大虾不吝赐教！

[bw555]

--设定表名为T
SELECT 姓名,销售金额,销售时间,零售单号, 店名 FROM 
(select 姓名,销售时间,零售单号, 店名,
ROW_NUMBER() OVER(PARTITION BY 零售单号 ORDER BY 销售金额 DESC) RN,
SUM(销售金额) OVER(PARTITION BY 零售单号)  销售金额  
FROM T)
WHERE RN=1

[bitter33]

ROW_NUMBER() OVER(PARTITION BY 零售单号 ORDER BY 销售金额 DESC) RN,这句话有什么作用不太懂、？、

[小灰狼W]

select max(姓名)keep(dense_rank last order by 销售金额,rowid), sum(销售金额), max(销售时间)keep(dense_rank last order by 销售金额,rowid), 零售单号, max(店名)keep(dense_rank last order by 销售金额,rowid) from 表 group by 零售单号

[bw555]

引用 2 楼 bitter33 的回复:

ROW_NUMBER() OVER(PARTITION BY 零售单号 ORDER BY 销售金额 DESC) RN,这句话有什么作用不太懂、？、

单据内部按金额排序，以便外层找到各个单据金额最大的记录

[不写代码的钦]

楼主查下这个分析函数怎么用就知道了。 ROW_NUMBER() 其实就是一个分区然后标记功能，排序后第几就标记为几。

[linghengmao]

引用 5 楼 wildwave 的回复:

select max(姓名)keep(dense_rank last order by 销售金额,rowid), sum(销售金额), max(销售时间)keep(dense_rank last order by 销售金额,rowid), 零售单号, max(店名)keep(dense_rank last order by 销售金额,rowid) from 表 group by 零售单号

我这里所说的表是经过分组函数查出来的，ROWID，在这里用不了。

[bw555]

引用 6 楼 linghengmao 的回复:

[quote=引用 5 楼 wildwave 的回复:] select max(姓名)keep(dense_rank last order by 销售金额,rowid), sum(销售金额), max(销售时间)keep(dense_rank last order by 销售金额,rowid), 零售单号, max(店名)keep(dense_rank last order by 销售金额,rowid) from 表 group by 零售单号

我这里所说的表是经过分组函数查出来的，ROWID，在这里用不了。[/quote] 换一个其他的字段，只要同一个单据中不重复就可以 dense_rank允许并列最大，因此需要order by的字段区分出每一条记录 或是 你试试我1楼的写法 这两种写法都可以，执行效率应该也没有太大差别 rank,dense_rank,row_number使用和区别

[linghengmao]

可以了。谢谢大家！特别是大牛bw55!

[linghengmao]

引用 1 楼 bw555 的回复:

--设定表名为T
SELECT 姓名,销售金额,销售时间,零售单号, 店名 FROM 
(select 姓名,销售时间,零售单号, 店名,
ROW_NUMBER() OVER(PARTITION BY 零售单号 ORDER BY 销售金额 DESC) RN,
SUM(销售金额) OVER(PARTITION BY 零售单号)  销售金额  
FROM T)
WHERE RN=1

因我这里说的表，是一个类似视图，以下是实际的语句，套用以上语法，提示“未明确定义列”，不知是哪里出了问题？ select B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from (select 导购员,零售日期,货位编码, 货位名称,实收金额, ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 实收金额 DESC) RN, sum(实收金额) OVER(PARTITION BY 单号) 实收金额 FROM ( select TO_CHAR(ls_date, 'YYYY-MM-DD') as 零售日期,ls_number as 单号, ls_fs.ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_fs.ls_hw_name as 货位名称, sum(ls_take_money) as 实收金额 from ls_fs where ls_number in (select ls_number from ls_fs WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss')) having sum(ls_take_money)>=500 group by ls_number) group by TO_CHAR(ls_date, 'YYYY-MM-DD') ,ls_number, ls_hw_code,ls_channel_person,ls_hw_name order by ls_Number ) ) B where RN=1

[bw555]

你外层把单号也select出来了，里面的查询也要相应的加上

select  B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from 
	(select  导购员,零售日期,单号,货位编码, 货位名称,实收金额,
	ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 实收金额 DESC) RN,
	sum(实收金额)  OVER(PARTITION BY 单号)  实收金额
	FROM(
		select TO_CHAR(ls_date, 'YYYY-MM-DD')  as 零售日期,ls_number as 单号, ls_fs.ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_fs.ls_hw_name as 货位名称,
		sum(ls_take_money) as 实收金额
		from ls_fs where ls_number in 
			(select  ls_number 
			from ls_fs
			WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss')) 
			having sum(ls_take_money)>=500
			group by ls_number) 
		group by TO_CHAR(ls_date, 'YYYY-MM-DD') ,ls_number, ls_hw_code,ls_channel_person,ls_hw_name
		order by ls_Number
		) 
	)B
where RN=1

[bw555]

建议写语句时层级尽可能清晰一点，这样更容易找到是哪出的问题，上面帮你把格式整理了下

[linghengmao]

谢谢bw55,提醒，但还是提示未明确定义列。不知道错在哪里？是不是说有相同的列名而不知取哪一个表的列呀？

[bw555]

你最里面的查询可以单独执行吗？ 嵌入到外层查询时，里层查询的order by 应该去掉

[bw555]

逻辑有点问题，套的层次太多，整理下，试试这个

select  B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from 
	(select TO_CHAR(ls_date, 'YYYY-MM-DD')  as 零售日期,ls_number as 单号, ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_hw_name as 货位名称,
	ROW_NUMBER() OVER(PARTITION BY ls_number ORDER BY ls_take_money DESC) RN,
	sum(ls_take_money)  OVER(PARTITION BY ls_number)  实收金额
	FROM ls_fs
	WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss'))
	) B
where RN=1 and 实收金额>=500
order by 单号