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

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

姓名     销售金额       销售时间       零售单号             店名
------------------------------------------------------------------------------------------
张三      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店

还请各位大虾不吝赐教！

14 个解决方案

#1

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

#2

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

#3

引用 2 楼 bitter33 的回复:

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

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

#4

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

#5

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 零售单号

#6

引用 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，在这里用不了。

#7

引用 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，在这里用不了。

换一个其他的字段，只要同一个单据中不重复就可以
dense_rank允许并列最大，因此需要order by的字段区分出每一条记录
或是
你试试我1楼的写法
这两种写法都可以，执行效率应该也没有太大差别
rank,dense_rank,row_number使用和区别

#8

引用 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

#9

你外层把单号也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

#10

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

#11

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

#12

你最里面的查询可以单独执行吗？

嵌入到外层查询时，里层查询的order  by 应该去掉

#13

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

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 单号

#14

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